Update Query with Parameter

  • Thread starter Thread starter sg
  • Start date Start date
S

sg

I have an Update Query that asks for 3 fields with parameters. The first
parameter [ID] is the criteria in the query to identify which record we are
updating. The second and third parameters (city and state) are sending the
new information to the table. However, sometimes I only have a city that
needs to be updated where the state needs to stay the same as what is already
in the table. If I don't fill in the 3rd parameter, the state is deleted
from the table. How do I get around this without having to type the state in
when the parameter prompts me?

Thanks in advance.
 
Use an IIF statement --
IIF([Forms]![YourFormName]![State] Is Null, tblYourTable.State,
[Forms]![YourFormName]![State])
 
Why didn't I think of that?!? Thanks so much for your quick reply.

KARL DEWEY said:
Use an IIF statement --
IIF([Forms]![YourFormName]![State] Is Null, tblYourTable.State,
[Forms]![YourFormName]![State])

--
Build a little, test a little.


sg said:
I have an Update Query that asks for 3 fields with parameters. The first
parameter [ID] is the criteria in the query to identify which record we are
updating. The second and third parameters (city and state) are sending the
new information to the table. However, sometimes I only have a city that
needs to be updated where the state needs to stay the same as what is already
in the table. If I don't fill in the 3rd parameter, the state is deleted
from the table. How do I get around this without having to type the state in
when the parameter prompts me?

Thanks in advance.
Click to expand...
Click to expand...
 
I have an Update Query that asks for 3 fields with parameters. The first
parameter [ID] is the criteria in the query to identify which record we are
updating. The second and third parameters (city and state) are sending the
new information to the table. However, sometimes I only have a city that
needs to be updated where the state needs to stay the same as what is already
in the table. If I don't fill in the 3rd parameter, the state is deleted
from the table. How do I get around this without having to type the state in
when the parameter prompts me?

Thanks in advance.
Click to expand...

Update the State to

NZ([Enter state:], [STATE])

to update the field to itself if the prompt is blank.
 
Back
Top