Date Range Parameters

[AlCamp]

I often use Date Range Parameters to allow users to select a report date
range.
Between [Start Date] And [End Date]

I also use...
Like [Enter Text or Number] & "*"
that will allow the user to just hit Enter in response to the [Enter Text or
Number] parameter... and retrieve ALL records.

How can I gain that criteria "flexibility" when using Date values?

If the user just hits the Enter key in response to the [Start Date]
parameter, and just the Enter key in response to the [End Date] parameter,
I'd like to return ALL dates.

I've thought of doing an IF where... if IsNull([Start Date]) then [Start
Date] = #1/1/1945#...
and [End Date] = #1/1/12050#. But, that's pretty ugly...

Thanks for any assistance in advance...

Al Camp

 

[Douglas J. Steele]

You can use SQL like:

WHERE MyDate = [Start Date] OR [Start Date] IS NULL

 

[PC Datasheet]

Between NZ([Forms]![MyForm]![MyStartDate], #1700-01-01#) and
NZ([Forms]![MyForm]![MyEndDate], #9999-12-31#)


Note: Modify the dates in the above to a range that won't possibly exclude
any of your records.

 

[AlCamp]

Excellent Doug. I knew there had to be a more elegant way...
Thanks a lot for that info.
Al Camp

You can use SQL like:

WHERE MyDate = [Start Date] OR [Start Date] IS NULL

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I often use Date Range Parameters to allow users to select a report date
range.
Between [Start Date] And [End Date]

I also use...
Like [Enter Text or Number] & "*"
that will allow the user to just hit Enter in response to the [Enter Text
or
Number] parameter... and retrieve ALL records.

How can I gain that criteria "flexibility" when using Date values?

If the user just hits the Enter key in response to the [Start Date]
parameter, and just the Enter key in response to the [End Date]
parameter,
I'd like to return ALL dates.

I've thought of doing an IF where... if IsNull([Start Date]) then [Start
Date] = #1/1/1945#...
and [End Date] = #1/1/12050#. But, that's pretty ugly...

Thanks for any assistance in advance...

Al Camp

 

[AlCamp]

PC,
Very interesting use of the NZ function... I will give it a try.
Thanks for the help.
Al Camp

Between NZ([Forms]![MyForm]![MyStartDate], #1700-01-01#) and
NZ([Forms]![MyForm]![MyEndDate], #9999-12-31#)


Note: Modify the dates in the above to a range that won't possibly exclude
any of your records.


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

I often use Date Range Parameters to allow users to select a report date
range.
Between [Start Date] And [End Date]

I also use...
Like [Enter Text or Number] & "*"
that will allow the user to just hit Enter in response to the [Enter Text or
Number] parameter... and retrieve ALL records.

How can I gain that criteria "flexibility" when using Date values?

If the user just hits the Enter key in response to the [Start Date]
parameter, and just the Enter key in response to the [End Date]
parameter,
I'd like to return ALL dates.

I've thought of doing an IF where... if IsNull([Start Date]) then [Start
Date] = #1/1/1945#...
and [End Date] = #1/1/12050#. But, that's pretty ugly...

Thanks for any assistance in advance...

Al Camp