Conditional text in query help. If text is blank return all.

[BradWFresno]

I have a form called "New Report" and I have 3 text boxes on it. There is a subform that pulls a query.



One field is SDATE (formatted for date)
One field is EDATE (Formatted for date)
One field is IDNUM (formatted as text)

In the query I want to filter by ID number if one is entered into the text box IDNUM. In the conditions field of the query for IDNUM I have the following.

IIf([Forms]![New Report]![IDNUM]=Null,Null,[Forms]![New Report]![IDNUM])

It somewhat works if I put an ID number in the field however if I leave it blank it does not ever return any records. When left blank I would like it to return all records.

Thank you,

Brad

[June7]

Cannot test for =Null. Review http://allenbrowne.com/casu-12.html

[Bob Fitz]

Try:
IIf([Forms]![New Report]![IDNUM] Is Null,Null,[Forms]![New Report]![IDNUM])

[BradWFresno]

I made some changes but still need help

I have a form called "New Report" and I have 3 text boxes on it. There is a subform that pulls a query.

One field is SDATE (formatted for date)
One field is EDATE (Formatted for date)
One field is IDNUM (formatted as text) default value abc

In the query I want to filter by ID number if one is entered into the text box IDNUM. In the conditions field of the query for IDNUM I have the following.

Code:
IIf([Forms]![New Report]![IDNUM]= "abc","2618",[Forms]![New Report]![IDNUM])
This will return all values relation to 2618 what do I need to change to get all values?

Thank you,

Brad

[BradWFresno]

Try:
IIf([Forms]![New Report]![IDNUM] Is Null,Null,[Forms]![New Report]![IDNUM])

this did not work.

[June7]

IDNUM is a text type field?

Try

LIKE [Forms]![New Report]![IDNUM] & "*"

[BradWFresno]

IDNUM is a text type field?

Try

LIKE [Forms]![New Report]![IDNUM] & "*"

This returned everything even if I put a specific value. I would like to have a specific value or Like "*" if there is not a specific value entered into the text field.

I tried
IIf([Forms]![New Report]![IDNUM]="",Like "*",[Forms]![New Report]![IDNUM])

but after I save it it turns into

IIf([Forms]![New Report]![IDNUM]="",([dbo_EMPLOYEES].[IDNUM]) Like "*",[Forms]![New Report]![IDNUM])

which does not work. So close...

[June7]

The LIKE operator and wildcard criteria should return only the records that match. This is a proven filter method for text type fields. Why would every record return? By 'into the text field' do you mean the IDNUM textbox on form?

[BradWFresno]

The LIKE operator and wildcard criteria should return only the records that match. This is a proven filter method for text type fields. Why would every record return? By 'into the text field' do you mean the IDNUM textbox on form?

You are correct I was mistaken as I did not close my form before I tested it was caching.

This did fix it Thank you!