Passing Dates to Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Michael - 01 Nov 2007 22:07 GMT

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.

Signature

Michael

Reply to this Message

Douglas J. Steele - 01 Nov 2007 22:13 GMT

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

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

Reply to this Message

Michael - 01 Nov 2007 22:29 GMT

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
[quoted text clipped - 4 lines]
>> they do not enter a date. I thought I did this with the LIKE operator,
>> but I'm having no luck. Any suggestions? Thanks.

Reply to this Message

fredg - 01 Nov 2007 22:24 GMT

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

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message

Michael - 01 Nov 2007 22:33 GMT

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

Michael

>> Hi Folks - I know I have done this before, but I'm stumped ....
>> Currently, I
[quoted text clipped - 21 lines]
>
> Change the Form, and Control names as needed.

Reply to this Message