Prompt for date criteria in query

[Guest]

I created a query in design view which includes an expression, giving a date
as the result. I want users to be able to enter beginning and end dates for
query result, however, when I test the criteria in design view, I only get
results when I enter a single date, and it only works when I place a # before
and after the date. When I tried Between And prompt, I get no records. I
tried entering the date in the prompt dialog box using the #, but still get
no records. Can anyone help on this?

 

[Joseph Meehan]

I created a query in design view which includes an expression, giving
a date as the result. I want users to be able to enter beginning and
end dates for query result, however, when I test the criteria in
design view, I only get results when I enter a single date, and it
only works when I place a # before and after the date. When I tried
Between And prompt, I get no records. I tried entering the date in
the prompt dialog box using the #, but still get no records. Can
anyone help on this?

From the Access help file:
Number signs (#) (or single (') quotation marks in ANSI-92) around an
element of an expression indicate that the element is a date/time value.
Microsoft Access automatically evaluates a value surrounded by number signs
(or single quotation marks) as a date/time value and lets you type the value
in any common date or time format.
You don't have to type number signs (or single quotation marks) around a
date/time value in a validation expression or a criteria expression for a
field whose data type is Date/Time. You can type the value in any common
date or time format and Microsoft Access automatically inserts the correct
signs around the value. Note that Access 2002 and later conforms to ANSI-92.
Microsoft Access displays the value according to the regional settings in
Microsoft Windows Control Panel. You can change the output format of a date
by using the Format property.

 

[fredg]

I created a query in design view which includes an expression, giving a date
as the result. I want users to be able to enter beginning and end dates for
query result, however, when I test the criteria in design view, I only get
results when I enter a single date, and it only works when I place a # before
and after the date. When I tried Between And prompt, I get no records. I
tried entering the date in the prompt dialog box using the #, but still get
no records. Can anyone help on this?

Is the expression [Date] column really a Date datatype?
If so:

In Query Design View, Click on Query + Parameters.
In the Parameter column, write the parameter and it's expected
datatype:

[Enter Start Date] Date/Time
[Enter End Date] Date/Time

Note the text within the brackets must be identical to the bracketed
text in the criteria column.

Then set the query criteria to:
Between [Enter Start Date] and [Enter End Date]

When prompted simply enter the dates using the U.S. month/day/year
format, i.e. 1/8/2007.
No #'s are needed.

If the expression [Date] column results in a non-Date datatype, you'll
need to post back.

 

[Guest]

fredg
Thank you thank you thank you!! I've been trying to fix this for days!! It
works!!
--
LB

I created a query in design view which includes an expression, giving a date
as the result. I want users to be able to enter beginning and end dates for
query result, however, when I test the criteria in design view, I only get
results when I enter a single date, and it only works when I place a # before
and after the date. When I tried Between And prompt, I get no records. I
tried entering the date in the prompt dialog box using the #, but still get
no records. Can anyone help on this?

Is the expression [Date] column really a Date datatype?
If so:

In Query Design View, Click on Query + Parameters.
In the Parameter column, write the parameter and it's expected
datatype:

[Enter Start Date] Date/Time
[Enter End Date] Date/Time

Note the text within the brackets must be identical to the bracketed
text in the criteria column.

Then set the query criteria to:
Between [Enter Start Date] and [Enter End Date]

When prompted simply enter the dates using the U.S. month/day/year
format, i.e. 1/8/2007.
No #'s are needed.

If the expression [Date] column results in a non-Date datatype, you'll
need to post back.