If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Update Query with Parameter
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. |
#2
|
|||
|
|||
Update Query with Parameter
Use an IIF statement --
IIF([Forms]![YourFormName]![State] Is Null, tblYourTable.State, [Forms]![YourFormName]![State]) -- Build a little, test a little. "sg" wrote: 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. |
#3
|
|||
|
|||
Update Query with Parameter
Why didn't I think of that?!? Thanks so much for your quick reply.
"KARL DEWEY" wrote: Use an IIF statement -- IIF([Forms]![YourFormName]![State] Is Null, tblYourTable.State, [Forms]![YourFormName]![State]) -- Build a little, test a little. "sg" wrote: 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. |
#4
|
|||
|
|||
Update Query with Parameter
On Thu, 27 May 2010 18:45:01 -0700, sg wrote:
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. Update the State to NZ([Enter state:], [STATE]) to update the field to itself if the prompt is blank. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|