Prev: Query SQL Syntax
Next: Help on muliple table queries
From: sg on 27 May 2010 21:45
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.
From: KARL DEWEY on 27 May 2010 22:01
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.
From: sg on 27 May 2010 22:12
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.
From: John W. Vinson on 27 May 2010 22:40
On Thu, 27 May 2010 18:45:01 -0700, sg <sg(a)discussions.microsoft.com> 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]
 | 
Pages: 1
Prev: Query SQL Syntax
Next: Help on muliple table queries