if statements in queries

[sandpking]

I am using fields on an initial form to query a second form. However,
the query is not working.

The two fields are first name and last name. If both first name and
last name are entered, it works fine, but if just first name is
entered, it produces no results no matter what.
Any help is appreciated. Thank in advance.


FIRST NAME
IIf([Forms]![Frm_Computer_Search]![FIRST Name] Is Null,Like "*",
[Forms]![Frm_Computer_Search]![FIRST Name])

LAST NAME
IIf([Forms]![Frm_Computer_Search]![Last Name] Is Null,Like "*",[Forms]!
[Frm_Computer_Search]![Last Name])

 

[bhicks11 via AccessMonster.com]

I believe you need to use the NZ() function within the IIF statement.
Something like this:

IIf(NZ([Forms]![Frm_Computer_Search]![FIRST Name]) Is Null

etc.


From MS Access Language Reference:

You can use the Nz function to return zero, a zero-length string (" "), or
another specified value when a Variant is Null. For example, you can use this
function to convert a Null value to another value and prevent it from
propagating through an expression.

Nz(variant, [valueifnull])

The Nz function has the following arguments.

Argument Description
variant A variable of data type Variant.
valueifnull Optional (unless used in a query). A Variant that supplies a
value to be returned if the variant argument is Null. This argument enables
you to return a value other than zero or a zero-length string.
Note If you use the Nz function in an expression in a query without using
the valueifnull argument, the results will be a zero-length string in the
fields that contain null values.



If the value of the variant argument is Null, the Nz function returns the
number zero or a zero-length string (always returns a zero-length string when
used in a query expression), depending on whether the context indicates the
value should be a number or a string. If the optional valueifnull argument is
included, then the Nz function will return the value specified by that
argument if the variant argument is Null. When used in a query expression,
the NZ function should always include the valueifnull argument,

If the value of variant isn't Null, then the Nz function returns the value of
variant.



Bonnie Hicks

http://www.dataplus-svc.com

I am using fields on an initial form to query a second form. However,
the query is not working.

The two fields are first name and last name. If both first name and
last name are entered, it works fine, but if just first name is
entered, it produces no results no matter what.
Any help is appreciated. Thank in advance.

FIRST NAME
IIf([Forms]![Frm_Computer_Search]![FIRST Name] Is Null,Like "*",
[Forms]![Frm_Computer_Search]![FIRST Name])

LAST NAME
IIf([Forms]![Frm_Computer_Search]![Last Name] Is Null,Like "*",[Forms]!
[Frm_Computer_Search]![Last Name])

 

[Ken Snell \(MVP\)]

How are you "querying" the second form? Are you trying to open that form via
DoCmd.OpenForm, and give it a filtering string (fourth argument of the
OpenForm method)? Or doing something else?

 

[sandpking]

I'm only querying the second form. the first form only provides the
information to query. I haven't dealt with filter strings perhaps
that is easier.

I open the second form using an OpenForm macro. from the search form.

SELECT Computer_Assignments.*
FROM Computer_Assignments
WHERE (((Computer_Assignments.[First name])=[Forms]!
[Frm_Computer_Search]![First Name])) OR (((Computer_Assignments.[Last
Name])=[Forms]![Frm_Computer_Search]![Last Name])) OR
(((Computer_Assignments.[Serial ])=[Forms]![Frm_Computer_Search]!
[Serial])) OR (((Computer_Assignments.[Tag ])=[Forms]!
[Frm_Computer_Search]![Tag]));

How are you "querying" the second form? Are you trying to open that form via
DoCmd.OpenForm, and give it a filtering string (fourth argument of the
OpenForm method)? Or doing something else?

--

        Ken Snell
<MS ACCESS MVP>

I am using fields on an initial form to query a second form.  However,
the query is not working.

The two fields are first name and last name.  If both first name and
last name are entered, it works fine, but if just first name is
entered, it produces no results no matter what.
Any help is appreciated.  Thank in advance.

FIRST NAME
IIf([Forms]![Frm_Computer_Search]![FIRST Name] Is Null,Like "*",
[Forms]![Frm_Computer_Search]![FIRST Name])

LAST NAME
IIf([Forms]![Frm_Computer_Search]![Last Name] Is Null,Like "*",[Forms]!
[Frm_Computer_Search]![Last Name])- Hide quoted text -

- Show quoted text -

 

[sandpking]

The NZ option didn't work. I get a message that the query is too
complex. Same as when I type

FIRST NAME

 

[bhicks11 via AccessMonster.com]

Try making anew query but don't use the designer - go to view - SQL and put
the SQL. Save it without going back to the design screen. See if it runs.

Bonnie

http://www.dataplus-svc.com

The NZ option didn't work. I get a message that the query is too
complex. Same as when I type

FIRST NAME

IIf([Forms]![Frm_Computer_Search]![FIRST Name] Is Null, True,
[Forms]![Frm_Computer_Search]![FIRST Name])

LAST NAME
IIf([Forms]![Frm_Computer_Search]![Last Name] Is Null, True,[Forms]!
[Frm_Computer_Search]![Last Name])

I am using fields on an initial form to query a second form.  However,
the query is not working.

[quoted text clipped - 11 lines]

IIf([Forms]![Frm_Computer_Search]![Last Name] Is Null,Like "*",[Forms]!
[Frm_Computer_Search]![Last Name])

 

[sandpking]

I still get the too complex message. I should have said that I have
Access 2003 and Windows XP SP2

Try making anew query but don't use the designer - go to view - SQL and put
the SQL.  Save it without going back to the design screen.  See if itruns.

Bonnie

http://www.dataplus-svc.com

The NZ option didn't work.  I get a message that the query is too
complex. Same as when I type

FIRST NAME

IIf([Forms]![Frm_Computer_Search]![FIRST Name] Is Null, True,
[Forms]![Frm_Computer_Search]![FIRST Name])
LAST NAME
IIf([Forms]![Frm_Computer_Search]![Last Name] Is Null, True,[Forms]!
[Frm_Computer_Search]![Last Name])
I am using fields on an initial form to query a second form.  However,
the query is not working.

[quoted text clipped - 11 lines]

IIf([Forms]![Frm_Computer_Search]![Last Name] Is Null,Like "*",[Forms]!
[Frm_Computer_Search]![Last Name])

 

[bhicks11 via AccessMonster.com]

I'm sorry I can't be more help. It's the limitation of Access. You can try
to make a query that's less complicated and then use it for the source of
another query to add more criteria.

Bonnie

http://www.dataplus-svc.com

I still get the too complex message. I should have said that I have
Access 2003 and Windows XP SP2

Try making anew query but don't use the designer - go to view - SQL and put
the SQL.  Save it without going back to the design screen.  See if it runs.

[quoted text clipped - 25 lines]

- Show quoted text -

 

[sandpking]

Maybe I'm making this to difficult. All I want to do is have 2
screens. The first one acts as a filter for the second one. I'm
currently using a query, but if they leave a field blank, the query
won't work right. There is no telling what information the user will
have.

I still get the too complex message.  I should have said that I have
Access 2003 and Windows XP SP2

Try making anew query but don't use the designer - go to view - SQL andput
the SQL.  Save it without going back to the design screen.  See if it runs.

Bonnie

http://www.dataplus-svc.com

The NZ option didn't work.  I get a message that the query is too
complex. Same as when I type
FIRST NAME
IIf([Forms]![Frm_Computer_Search]![FIRST Name] Is Null, True,
[Forms]![Frm_Computer_Search]![FIRST Name])
LAST NAME
IIf([Forms]![Frm_Computer_Search]![Last Name] Is Null, True,[Forms]!
[Frm_Computer_Search]![Last Name])
I am using fields on an initial form to query a second form.  However,
the query is not working.
[quoted text clipped - 11 lines]
IIf([Forms]![Frm_Computer_Search]![Last Name] Is Null,Like "*",[Forms]!
[Frm_Computer_Search]![Last Name])

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Ken Snell \(MVP\)]

Try this:

SELECT Computer_Assignments.*
FROM Computer_Assignments
WHERE (Computer_Assignments.[First name]=[Forms]!
[Frm_Computer_Search]![First Name] OR [Forms]!
[Frm_Computer_Search]![First Name] Is Null) AND (Computer_Assignments.[Last
Name]=[Forms]![Frm_Computer_Search]![Last Name] OR
[Forms]![Frm_Computer_Search]![Last Name] Is Null) AND
(Computer_Assignments.[Serial]=[Forms]![Frm_Computer_Search]!
[Serial] OR [Forms]![Frm_Computer_Search]!
[Serial] Is Null) AND (Computer_Assignments.[Tag])=[Forms]!
[Frm_Computer_Search]![Tag] OR [Forms]!
[Frm_Computer_Search]![Tag] Is Null);

--

Ken Snell
<MS ACCESS MVP>


I'm only querying the second form. the first form only provides the
information to query. I haven't dealt with filter strings perhaps
that is easier.

I open the second form using an OpenForm macro. from the search form.

SELECT Computer_Assignments.*
FROM Computer_Assignments
WHERE (((Computer_Assignments.[First name])=[Forms]!
[Frm_Computer_Search]![First Name])) OR (((Computer_Assignments.[Last
Name])=[Forms]![Frm_Computer_Search]![Last Name])) OR
(((Computer_Assignments.[Serial ])=[Forms]![Frm_Computer_Search]!
[Serial])) OR (((Computer_Assignments.[Tag ])=[Forms]!
[Frm_Computer_Search]![Tag]));

How are you "querying" the second form? Are you trying to open that form
via
DoCmd.OpenForm, and give it a filtering string (fourth argument of the
OpenForm method)? Or doing something else?

--

Ken Snell
<MS ACCESS MVP>

I am using fields on an initial form to query a second form. However,
the query is not working.

The two fields are first name and last name. If both first name and
last name are entered, it works fine, but if just first name is
entered, it produces no results no matter what.
Any help is appreciated. Thank in advance.

FIRST NAME
IIf([Forms]![Frm_Computer_Search]![FIRST Name] Is Null,Like "*",
[Forms]![Frm_Computer_Search]![FIRST Name])

LAST NAME
IIf([Forms]![Frm_Computer_Search]![Last Name] Is Null,Like "*",[Forms]!
[Frm_Computer_Search]![Last Name])- Hide quoted text -

- Show quoted text -

 

[bhicks11 via AccessMonster.com]

Maybe. Design a simple form - no data source. Put a control on it - make it
a combobox. Set the data source of the combobox to a Table/Query and design
a query in the row source that shows something you would like the user to
select.

Go to the Events tab and make an Exit event that opens the second form. Set
the default value of the control on the second form that you want to the
value of the control on the first form.

Start there and get more complicated.

Bonnie

http://www.dataplus-svc.com

Maybe I'm making this to difficult. All I want to do is have 2
screens. The first one acts as a filter for the second one. I'm
currently using a query, but if they leave a field blank, the query
won't work right. There is no telling what information the user will
have.

I still get the too complex message.  I should have said that I have
Access 2003 and Windows XP SP2

[quoted text clipped - 32 lines]

- Show quoted text -

 

[sandpking]

I got an invalid use of '.', "!", or () in a query expression. I saw
that there was a left over parenthesis after [Tag]) which I removed.

It seems as if this would be easier. If I had 2 fields on the first
form and the user left one blank, it seems it would be easy to make a
query to use the information provided to return a result. Perhaps a
filter is a better choice?

Try this:

SELECT Computer_Assignments.*
FROM Computer_Assignments
WHERE (Computer_Assignments.[First name]=[Forms]!
[Frm_Computer_Search]![First Name] OR [Forms]!
[Frm_Computer_Search]![First Name] Is Null) AND (Computer_Assignments.[Last
Name]=[Forms]![Frm_Computer_Search]![Last Name] OR
[Forms]![Frm_Computer_Search]![Last Name] Is Null) AND
(Computer_Assignments.[Serial]=[Forms]![Frm_Computer_Search]!
[Serial] OR [Forms]![Frm_Computer_Search]!
[Serial] Is Null) AND (Computer_Assignments.[Tag])=[Forms]!
[Frm_Computer_Search]![Tag] OR [Forms]!
[Frm_Computer_Search]![Tag] Is Null);

--

        Ken Snell
<MS ACCESS MVP>


I'm only querying the second form.  the first form only provides the
information to query.  I haven't dealt with filter strings perhaps
that is easier.

I open the second form using an OpenForm macro. from the search form.

SELECT Computer_Assignments.*
FROM Computer_Assignments
WHERE (((Computer_Assignments.[First name])=[Forms]!
[Frm_Computer_Search]![First Name])) OR (((Computer_Assignments.[Last
Name])=[Forms]![Frm_Computer_Search]![Last Name])) OR
(((Computer_Assignments.[Serial ])=[Forms]![Frm_Computer_Search]!
[Serial])) OR (((Computer_Assignments.[Tag ])=[Forms]!
[Frm_Computer_Search]![Tag]));

How are you "querying" the second form? Are you trying to open that form
via
DoCmd.OpenForm, and give it a filtering string (fourth argument of the
OpenForm method)? Or doing something else?

Ken Snell
<MS ACCESS MVP>

news:8e188e76-308d-476a-8939-8ad94d5600ac@d19g2000prm.googlegroups.com....

I am using fields on an initial form to query a second form. However,
the query is not working.
The two fields are first name and last name. If both first name and
last name are entered, it works fine, but if just first name is
entered, it produces no results no matter what.
Any help is appreciated. Thank in advance.
FIRST NAME
IIf([Forms]![Frm_Computer_Search]![FIRST Name] Is Null,Like "*",
[Forms]![Frm_Computer_Search]![FIRST Name])
LAST NAME
IIf([Forms]![Frm_Computer_Search]![Last Name] Is Null,Like "*",[Forms]!
[Frm_Computer_Search]![Last Name])- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[Ken Snell \(MVP\)]

I don't use Filters very much in my forms; preferring instead to change the
SQL statement of the form's RecordSource query so that it filters within the
query itself.

Although it may be a bit more detailed than you seek, I have a sample
database here that shows how to build SQL queries based on various controls
on a form:
http://www.accessmvp.com/KDSnell/SampleDBs.htm#FilterForm

--

Ken Snell
<MS ACCESS MVP>



I got an invalid use of '.', "!", or () in a query expression. I saw
that there was a left over parenthesis after [Tag]) which I removed.

It seems as if this would be easier. If I had 2 fields on the first
form and the user left one blank, it seems it would be easy to make a
query to use the information provided to return a result. Perhaps a
filter is a better choice?

Try this:

SELECT Computer_Assignments.*
FROM Computer_Assignments
WHERE (Computer_Assignments.[First name]=[Forms]!
[Frm_Computer_Search]![First Name] OR [Forms]!
[Frm_Computer_Search]![First Name] Is Null) AND
(Computer_Assignments.[Last
Name]=[Forms]![Frm_Computer_Search]![Last Name] OR
[Forms]![Frm_Computer_Search]![Last Name] Is Null) AND
(Computer_Assignments.[Serial]=[Forms]![Frm_Computer_Search]!
[Serial] OR [Forms]![Frm_Computer_Search]!
[Serial] Is Null) AND (Computer_Assignments.[Tag])=[Forms]!
[Frm_Computer_Search]![Tag] OR [Forms]!
[Frm_Computer_Search]![Tag] Is Null);

--

Ken Snell
<MS ACCESS MVP>


I'm only querying the second form. the first form only provides the
information to query. I haven't dealt with filter strings perhaps
that is easier.

I open the second form using an OpenForm macro. from the search form.

SELECT Computer_Assignments.*
FROM Computer_Assignments
WHERE (((Computer_Assignments.[First name])=[Forms]!
[Frm_Computer_Search]![First Name])) OR (((Computer_Assignments.[Last
Name])=[Forms]![Frm_Computer_Search]![Last Name])) OR
(((Computer_Assignments.[Serial ])=[Forms]![Frm_Computer_Search]!
[Serial])) OR (((Computer_Assignments.[Tag ])=[Forms]!
[Frm_Computer_Search]![Tag]));

How are you "querying" the second form? Are you trying to open that form
via
DoCmd.OpenForm, and give it a filtering string (fourth argument of the
OpenForm method)? Or doing something else?

Ken Snell
<MS ACCESS MVP>

news:8e188e76-308d-476a-8939-8ad94d5600ac@d19g2000prm.googlegroups.com...

I am using fields on an initial form to query a second form. However,
the query is not working.
The two fields are first name and last name. If both first name and
last name are entered, it works fine, but if just first name is
entered, it produces no results no matter what.
Any help is appreciated. Thank in advance.
FIRST NAME
IIf([Forms]![Frm_Computer_Search]![FIRST Name] Is Null,Like "*",
[Forms]![Frm_Computer_Search]![FIRST Name])
LAST NAME
IIf([Forms]![Frm_Computer_Search]![Last Name] Is Null,Like
"*",[Forms]!
[Frm_Computer_Search]![Last Name])- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[sandpking]

I ended up making an exact copy of the edit screen with all the fields
and changing it so that it couldn't be edited, added to, or deleted
from. I then added a button to call up the edit screen. That way the
user could search to his hearts content and then when he found the
record he wanted, he could hit the edit button to call up the edit
screen

I don't use Filters very much in my forms; preferring instead to change the
SQL statement of the form's RecordSource query so that it filters within the
query itself.

Although it may be a bit more detailed than you seek, I have a sample
database here that shows how to build SQL queries based on various controls
on a form:http://www.accessmvp.com/KDSnell/SampleDBs.htm#FilterForm

--

        Ken Snell
<MS ACCESS MVP>


I got an invalid use of '.', "!", or () in a query expression.  I saw
that there was a left over parenthesis after [Tag]) which I removed.

It seems as if this would be easier.  If I had 2 fields on the first
form and the user left one blank, it seems it would be easy to make a
query to use the information provided to return a result.  Perhaps a
filter is a better choice?

Try this:

SELECT Computer_Assignments.*
FROM Computer_Assignments
WHERE (Computer_Assignments.[First name]=[Forms]!
[Frm_Computer_Search]![First Name] OR [Forms]!
[Frm_Computer_Search]![First Name] Is Null) AND
(Computer_Assignments.[Last
Name]=[Forms]![Frm_Computer_Search]![Last Name] OR
[Forms]![Frm_Computer_Search]![Last Name] Is Null) AND
(Computer_Assignments.[Serial]=[Forms]![Frm_Computer_Search]!
[Serial] OR [Forms]![Frm_Computer_Search]!
[Serial] Is Null) AND (Computer_Assignments.[Tag])=[Forms]!
[Frm_Computer_Search]![Tag] OR [Forms]!
[Frm_Computer_Search]![Tag] Is Null);

--

Ken Snell
<MS ACCESS MVP>

<[email protected]> wrote in message

I'm only querying the second form. the first form only provides the
information to query. I haven't dealt with filter strings perhaps
that is easier.

I open the second form using an OpenForm macro. from the search form.

SELECT Computer_Assignments.*
FROM Computer_Assignments
WHERE (((Computer_Assignments.[First name])=[Forms]!
[Frm_Computer_Search]![First Name])) OR (((Computer_Assignments.[Last
Name])=[Forms]![Frm_Computer_Search]![Last Name])) OR
(((Computer_Assignments.[Serial ])=[Forms]![Frm_Computer_Search]!
[Serial])) OR (((Computer_Assignments.[Tag ])=[Forms]!
[Frm_Computer_Search]![Tag]));

How are you "querying" the second form? Are you trying to open that form
via
DoCmd.OpenForm, and give it a filtering string (fourth argument of the
OpenForm method)? Or doing something else?
--
Ken Snell
<MS ACCESS MVP>

I am using fields on an initial form to query a second form. However,
the query is not working.
The two fields are first name and last name. If both first name and
last name are entered, it works fine, but if just first name is
entered, it produces no results no matter what.
Any help is appreciated. Thank in advance.
FIRST NAME
IIf([Forms]![Frm_Computer_Search]![FIRST Name] Is Null,Like "*",
[Forms]![Frm_Computer_Search]![FIRST Name])
LAST NAME
IIf([Forms]![Frm_Computer_Search]![Last Name] Is Null,Like
"*",[Forms]!
[Frm_Computer_Search]![Last Name])- Hide quoted text -
- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

 

[bhicks11 via AccessMonster.com]

Good going sandpking. Glad you found your solution!

Bonnie
http://www.dataplus-svc.com

I ended up making an exact copy of the edit screen with all the fields
and changing it so that it couldn't be edited, added to, or deleted
from. I then added a button to call up the edit screen. That way the
user could search to his hearts content and then when he found the
record he wanted, he could hit the edit button to call up the edit
screen

I don't use Filters very much in my forms; preferring instead to change the
SQL statement of the form's RecordSource query so that it filters within the

[quoted text clipped - 101 lines]

- Show quoted text -