Query for date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

HI,
I would like to filter by query for certain date. I know how to use today
Date(), but I would like to be able to put any date. Date range might work,
but it would require to input the same date twice. So far I was able to find
an answer on this forum.
Thanks in advance
 
I am not too sure what is the problem you experienced from your description
but to query for a certain date, you can use the criterion like:

.... WHERE [YourDateField] = #09/30/2005#

Notes:
====
1. Explicit date must be in the US format "mm/dd/yyyy" and enclosed in
hashes (#). You can also use the (unambiguous) format "yyyy-mm-dd" but
Access will convert it to the "mm/dd/yyyy" format.

2. If you have non-zero time component in [YourDateField], you need to
eliminate the time component using DateValue() like:

.... WHERE DateValue([YourDateField]) = #09/30/2005#

Alternatively, specify a range like:

.... WHERE ([YourDateField] >= #09/30/2005#)
And ([YourDateField] < #10/01/2005#)

The range looks a bit more complex but I think it is more efficient than
using DateValue() and hence my preference is the range rather than
DateValue(), certainly for Table with lots of Records and an index on the
date Field.
 
I would like to have a pop up window asking my what date I would like to
filter for. You formula do not seem to allow that.

Van T. Dinh said:
I am not too sure what is the problem you experienced from your description
but to query for a certain date, you can use the criterion like:

.... WHERE [YourDateField] = #09/30/2005#

Notes:
====
1. Explicit date must be in the US format "mm/dd/yyyy" and enclosed in
hashes (#). You can also use the (unambiguous) format "yyyy-mm-dd" but
Access will convert it to the "mm/dd/yyyy" format.

2. If you have non-zero time component in [YourDateField], you need to
eliminate the time component using DateValue() like:

.... WHERE DateValue([YourDateField]) = #09/30/2005#

Alternatively, specify a range like:

.... WHERE ([YourDateField] >= #09/30/2005#)
And ([YourDateField] < #10/01/2005#)

The range looks a bit more complex but I think it is more efficient than
using DateValue() and hence my preference is the range rather than
DateValue(), certainly for Table with lots of Records and an index on the
date Field.

--
HTH
Van T. Dinh
MVP (Access)


alexasha said:
HI,
I would like to filter by query for certain date. I know how to use today
Date(), but I would like to be able to put any date. Date range might
work,
but it would require to input the same date twice. So far I was able to
find
an answer on this forum.
Thanks in advance
Click to expand...
Click to expand...
 
It is called a Paramter Query.

You can simply set your criteria to:

.... WHERE [YourDateField] = [Enter required date:]
 
Alexasha

If you are working directly in SQL, Van's post describes a solution.

If you are working exclusively in the query design window, you can still use
(a portion of) his solution. In the criterion row, under your datefield,
enter a parameter/prompt:
[Enter required date:]
This query will now prompt you.

You may need to set the property of this prompt/parameter to DateTime.
 
Thanks, that was very easy

Jeff Boyce said:
Alexasha

If you are working directly in SQL, Van's post describes a solution.

If you are working exclusively in the query design window, you can still use
(a portion of) his solution. In the criterion row, under your datefield,
enter a parameter/prompt:
[Enter required date:]
This query will now prompt you.

You may need to set the property of this prompt/parameter to DateTime.

--
Regards

Jeff Boyce
<Office/Access MVP>

alexasha said:
HI,
I would like to filter by query for certain date. I know how to use today
Date(), but I would like to be able to put any date. Date range might work,
but it would require to input the same date twice. So far I was able to find
an answer on this forum.
Thanks in advance
Click to expand...
Click to expand...
 
Back
Top