Update Query Question

[magmike]

In using the Query Design View for an Update Query, it shows "Update
To:" in which I have put [New Date] and "Criteria:" in which I have
put, [Old Date]; and it therefore asks for the "New Date" first. That
just feels backward to me. But how do I get it to ask for the Criteria
first?

Thanks in advance!

magmike

 

[Pete D.]

Ask for, sounds like you have invalid fields in the wrong place.
The criteria is why you want to update it such as
update employee name to Fred if item is stored in management office

[Employee name] being the field to update
To "Fred" being what to change it to
If Criteria, Field [office] is equal to "management office".

HTH, Pete

 

[John Spencer]

Try declaring the prompts as parameters in the order you want to use them

Parameters [Old Date] DateTime, [New Date] DateTime;
UPDATE YourTable
SET YourField = [New Date]
WHERE YourField = [Old Date]

To add the parameters using the query design view

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

 

[magmike]

Ask for, sounds like you have invalid fields in the wrong place.
The criteria is why you want to update it such as
update employee name to Fred if item is stored in management office

[Employee name] being the field to update
To "Fred" being what to change it to
If Criteria, Field [office] is equal to "management office".

HTH, Pete

In using the Query Design View for an Update Query, it shows "Update
To:" in which I have put [New Date] and "Criteria:" in which I have
put, [Old Date]; and it therefore asks for the "New Date" first. That
just feels backward to me. But how do I get it to ask for the Criteria
first?

Thanks in advance!

magmike- Hide quoted text -

- Show quoted text -

That's why the Criteria is [Old Date] - so that I can tell it at the
time what the criteria is - such as, changing 8/4/2008 to 8/12/2008.
Since there is no field in the query named [Old Date], I get a prompt
to provide that data, which is exactly what I want, just before the
updated data is entered.

 

[magmike]

Try declaring the prompts as parameters in the order you want to use them

Parameters [Old Date] DateTime, [New Date] DateTime;
UPDATE YourTable
SET YourField = [New Date]
WHERE YourField = [Old Date]

To add the parameters using the query design view

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

Should I use the RunSQL command in code view to do this? I made the
perameter additions to the query and changed to Select query and it
doesn't update.

 

[John Spencer]

You should not change the query to a SELECT query. It should remain an UPDATE
query.

I may have mislead you with the line
Select Query: Parameters from the Menu
By select I meant Click on QUERY: Parameters in the menu. That will open the
parameters dialog. The second set of instructions was an alternative to
directly editing the SQL statement.

If you are going to try to do this from VBA, you can use DoCmd.RunSQL and the
SQL string. I would normally use the execute method of a database reference
and a modification to the SQL string if I were going to use VBA.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Try declaring the prompts as parameters in the order you want to use them

Parameters [Old Date] DateTime, [New Date] DateTime;
UPDATE YourTable
SET YourField = [New Date]
WHERE YourField = [Old Date]

To add the parameters using the query design view

Open the query in design mode
Select Query: Parameters from the Menu
Fill in the EXACT name of the parameter in column 1
Select the data type of the parameter in column 2

Should I use the RunSQL command in code view to do this? I made the
perameter additions to the query and changed to Select query and it
doesn't update.