A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Update Query with Parameter



 
 
Thread Tools Display Modes
  #1  
Old May 28th, 2010, 02:45 AM posted to microsoft.public.access.queries
sg
external usenet poster
 
Posts: 61
Default 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  
Old May 28th, 2010, 03:01 AM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default 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  
Old May 28th, 2010, 03:12 AM posted to microsoft.public.access.queries
sg
external usenet poster
 
Posts: 61
Default 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  
Old May 28th, 2010, 03:40 AM posted to microsoft.public.access.queries
John W. Vinson
external usenet poster
 
Posts: 18,261
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:55 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.