How can I use wild cards in Date fields?

  • Thread starter Thread starter hanski
  • Start date Start date
H

hanski

Hi.

I have a query which asks user to give StartingDate and ClosingDate.

It´s like this: Between [Give StartingDate:] And [Give ClosingDate].
It´s working.

BUT if user want´s to see all events (that is: He/She do not want to
restrict events by date) it won´t work.

I tried to use this: Like "*" & (Between [Give StartingDate:] And
[Give ClosingDate:]) & "*", but it does not give me the right answer.

How can user either give date range or if he/she push enter-button,
query will give him/her all events?

Hope you understand what I mean...


Hannu
 
Wildcards only with with string data, so it is very inefficient to try ot
use them with date/time fields.

Would you consider creating a small form with a couple of unbound text boxes
where the user can enter the limiting dates? You can then use the approach
in method 2 of this article:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html
Although the article is about a report, you can do exactly the same thing in
a Filter of a form.

Or you could build the WHERE clause in your query to return True if the text
boxes are null. The WHERE clause of the query would look like this:

WHERE (([Forms].[frmWhatDates].[txtStartDate] Is Null)
OR ([MyDate] >= [Forms].[frmWhatDates].[txtStartDate]))
AND (([Forms].[frmWhatDates].[txtEndDate] Is Null)
OR ([MyDate] < [Forms].[frmWhatDates].[txtEndDate] + 1))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi.

I have a query which asks user to give StartingDate and ClosingDate.

It´s like this: Between [Give StartingDate:] And [Give ClosingDate].
It´s working.

BUT if user want´s to see all events (that is: He/She do not want to
restrict events by date) it won´t work.

I tried to use this: Like "*" & (Between [Give StartingDate:] And
[Give ClosingDate:]) & "*", but it does not give me the right answer.

How can user either give date range or if he/she push enter-button,
query will give him/her all events?

Hope you understand what I mean...


Hannu
 
Hi Hanski

When the parameter box opens asking for dates don't enter anything just
press enter and see if you get what you want
 
You can try:
Between Nz([Give Starting Date],[YourDateField]) And Nz([Give Closing
Date],[YourDateField])
This won't return records where [YourDateField] is null.
--
Duane Hookom
MS Access MVP

Hi.

I have a query which asks user to give StartingDate and ClosingDate.

It´s like this: Between [Give StartingDate:] And [Give ClosingDate].
It´s working.

BUT if user want´s to see all events (that is: He/She do not want to
restrict events by date) it won´t work.

I tried to use this: Like "*" & (Between [Give StartingDate:] And
[Give ClosingDate:]) & "*", but it does not give me the right answer.

How can user either give date range or if he/she push enter-button,
query will give him/her all events?

Hope you understand what I mean...


Hannu
 
Use 2 queries. Use a form to enter the dates.

One query has no criteria for dates. Call it AllRecords. Use a form called
Select Dates with unbound controls for Start and Close dates and a command
button. Make a second query called FilteredRecords and set its criteria for
the date field reference the forms Start and Close dates controls. For
example - Between [Forms]![Select Dates]![Start] And [Forms]![Select
Dates]![Close]. When he/she pushes button run an If...Then statement such
as:

Private Sub cmdButton_Click()

If IsNull(Me.Start) Then
DoCmd.OpenQuery "AllRecords"
Else
DoCmd.OpenQuery "FilteredRecords"
End If

It's better to use a form for entering criteria anyway because you can add
other options like to check if the Start Date is after the End Date.

May be a better way but it should work.


Hi.

I have a query which asks user to give StartingDate and ClosingDate.

It´s like this: Between [Give StartingDate:] And [Give ClosingDate].
It´s working.

BUT if user want´s to see all events (that is: He/She do not want to
restrict events by date) it won´t work.

I tried to use this: Like "*" & (Between [Give StartingDate:] And
[Give ClosingDate:]) & "*", but it does not give me the right answer.

How can user either give date range or if he/she push enter-button,
query will give him/her all events?

Hope you understand what I mean...


Hannu
 
Back
Top