query expression

A

alm09

I have a combo box on a form that is used enter a parameter for a query. I
would like the user to have the option of either entering an acct # to
specify an account to query on or leaving the field blank and thus running
the query on all accounts. Below is the criteria field in the query. Is this
the right approach? If so, how do I tell it to give me all accounts should an
acct # not be specified?


=IIf(IsNull([Forms]![XYZ Form]![Combo5]),??????,[Forms]![XYZ Form]![Combo5])
 
O

Ofer Cohen

Use this criteria for the field

[Forms]![XYZ Form]![Combo5]

Add another field to the query
[Forms]![XYZ Form]![Combo5]

And in the second line criteria (Or) write - Is Null

In SQL it will look like
Select * From TableName Where FieldName = [Forms]![XYZ Form]![Combo5] Or
[Forms]![XYZ Form]![Combo5] Is Null
 
A

alm09

That did not work.

When I leave the combo box blank/null, the query ran but returned 0 records.
This is the only criteria for the query.

Any other ideas?







Klatuu said:
LIKE IIf(IsNull([Forms]![XYZ Form]![Combo5]),"*",[Forms]![XYZ Form]![Combo5])
--
Dave Hargis, Microsoft Access MVP


alm09 said:
I have a combo box on a form that is used enter a parameter for a query. I
would like the user to have the option of either entering an acct # to
specify an account to query on or leaving the field blank and thus running
the query on all accounts. Below is the criteria field in the query. Is this
the right approach? If so, how do I tell it to give me all accounts should an
acct # not be specified?


=IIf(IsNull([Forms]![XYZ Form]![Combo5]),??????,[Forms]![XYZ Form]![Combo5])
Click to expand...
Click to expand...
 
O

Ofer Cohen

Have you tried my suggestion?

Also, it could be that the combo doesn't return Null, it might return an
empty value.
You can check the value returned by openning the Immidiate window (Ctrl+G)
and type
?Forms![XYZ Form]![Combo5]

Press Enter and see the value retured.

If its empty you can try:
LIKE IIf(Len(Trim([Forms]![XYZ Form]![Combo5]) & "") = 0,"*",[Forms]![XYZ
Form]![Combo5])

--
Good Luck
BS"D


alm09 said:
That did not work.

When I leave the combo box blank/null, the query ran but returned 0 records.
This is the only criteria for the query.

Any other ideas?







Klatuu said:
LIKE IIf(IsNull([Forms]![XYZ Form]![Combo5]),"*",[Forms]![XYZ Form]![Combo5])
--
Dave Hargis, Microsoft Access MVP


alm09 said:
I have a combo box on a form that is used enter a parameter for a query. I
would like the user to have the option of either entering an acct # to
specify an account to query on or leaving the field blank and thus running
the query on all accounts. Below is the criteria field in the query. Is this
the right approach? If so, how do I tell it to give me all accounts should an
acct # not be specified?


=IIf(IsNull([Forms]![XYZ Form]![Combo5]),??????,[Forms]![XYZ Form]![Combo5])
Click to expand...
Click to expand...
Click to expand...
 
Top