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])

 

[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

 

[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])

 

[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 -

 

[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 -