Query Criteria - All Results If Blank (1 Viewer)

[KirkComer]

I use this on most queries where I need to return all results if the form field is left blank. Works like a charm every time...

Like [Forms]![frm_main_menu]![Week] & "*" Or Is Null

except for this time...
I need to filter by week number (52 weeks in a year)...
problem is if I enter week "1" I also get weeks "10, 11, 12, 13, 14, 15, 16, 17, 18 and 19".

Any suggestions?

 

[Royce]

Try something like this:

Like IIf(Nz([Forms]![frm_main_menu]![Week],"*")="*","*",[Forms]![frm_main_menu]![Week])

 

[Brianwarnock]

I use this on most queries where I need to return all results if the form field is left blank. Works like a charm every time...

Like [Forms]![frm_main_menu]![Week] & "*" Or Is Null

except for this time...
I need to filter by week number (52 weeks in a year)...
problem is if I enter week "1" I also get weeks "10, 11, 12, 13, 14, 15, 16, 17, 18 and 19".

Any suggestions?

Don't use Like use

Yourfield=[Forms]![frm_main_menu]![Week] or [Forms]![frm_main_menu]![Week] Is Null

Brian

 

[Brianwarnock]

BTW I don't see why you need the Is Null with your Like as if the form is null you will get Like "*"

Brian

 

[KirkComer]

Don't use Like use

Yourfield=[Forms]![frm_main_menu]![Week] or [Forms]![frm_main_menu]![Week] Is Null

Brian

Thanks Brian and Royce. I didn't try Royce's suggestion but Brian's worked perfectly.

Thanks Again,
Kirk