if statements in queries

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

wrote:
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.

On Jun 23, 1:17Â*pm, wrote:
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 -

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200806/1

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?

On Jun 23, 6:27*pm, "Ken Snell \(MVP\)"
wrote:
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

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

On Jun 20, 8:36 pm, "Ken Snell \(MVP\)"



wrote:
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

wrote in message

....

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 -

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/Sam...htm#FilterForm

--

Ken Snell
MS ACCESS MVP



wrote in message
...
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?

On Jun 23, 6:27 pm, "Ken Snell \(MVP\)"
wrote:
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

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

On Jun 20, 8:36 pm, "Ken Snell \(MVP\)"



wrote:
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

wrote in message

...

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 -

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





On Jun 26, 4:57*pm, "Ken Snell \(MVP\)"
wrote:

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/Sam...htm#FilterForm

--

* * * * Ken Snell
MS ACCESS MVP

wrote in message

...
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?

On Jun 23, 6:27 pm, "Ken Snell \(MVP\)"



wrote:
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

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

On Jun 20, 8:36 pm, "Ken Snell \(MVP\)"

wrote:
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

wrote in message

....

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 -

Good going sandpking. Glad you found your solution!

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

wrote:
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

On Jun 26, 4:57Â*pm, "Ken Snell \(MVP\)"
wrote:

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 -

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200807/1