Passing Dates to Query

[Michael]

Hi Folks - I know I have done this before, but I'm stumped .... Currently, I
am passing dates from a form to query criteria. If I type the dates in, the
query works fine. However, I want all dates selected if they do not enter a
date. I thought I did this with the LIKE operator, but I'm having no luck.
Any suggestions? Thanks.

 

[Douglas J. Steele]

WHERE MyDateField = Forms!MyForms!DateField OR Forms!MyForms!DateField IS
NULL

 

[fredg]

Hi Folks - I know I have done this before, but I'm stumped .... Currently, I
am passing dates from a form to query criteria. If I type the dates in, the
query works fine. However, I want all dates selected if they do not enter a
date. I thought I did this with the LIKE operator, but I'm having no luck.
Any suggestions? Thanks.

As criteria on the Date field, write:

Between Forms!FormName!StartDate and Forms!FormName!EndDate

Then on the OR line (below the above), write:

Forms!FormName!StartDate Is Null

If just the StartDate is Null, you will get all of the records.

If you need both being null then use:

Forms!FormName!StartDate Is Null AND Forms!FormName!EndDate Is Null

Change the Form, and Control names as needed.

 

[Michael]

I tried a variation of that, but no luck ... here's what I have:

Between [Forms]![frmCase]![txtStartScreenDate] And
[Forms]![frmCase]![txtEndScreenDate] Or Is Null

When there are no dates, works fine, but when there is dates, no go ....
Thanks for your help.

Michael

WHERE MyDateField = Forms!MyForms!DateField OR Forms!MyForms!DateField IS
NULL

 

[Michael]

Got it ...... Thanks!!!


Michael

As criteria on the Date field, write:

Between Forms!FormName!StartDate and Forms!FormName!EndDate

Then on the OR line (below the above), write:

Forms!FormName!StartDate Is Null

If just the StartDate is Null, you will get all of the records.

If you need both being null then use:

Forms!FormName!StartDate Is Null AND Forms!FormName!EndDate Is Null

Change the Form, and Control names as needed.