Date Parameter Values

B

BrianP

I have several queries that ask for Between [BegDate] and [EndDate] via a
parameter value dialog box and I have other queries that contain hard coded
date values. But, what I need now is a query that prompts the user to enter a
single [ReportDate] value and then returns all items meeting that specific
report date criteria. What is the syntax or sql statement that I would use?
Any help would be greatly appreciated. Thanks.
 
O

Ofer Cohen

The SQL will be

Select * From TableName Where ReportDate = [Please select Report Date]

So, the criteria under the date field name will be [Please select Report
Date], and the user will be prompt with this message.
 
B

BrianP

That is what I tried originally and the query results shows no matching
records. However, when I run the query with the parameter Between [Starting]
and [Ending] and enter for example 5/31/07 as my start date and 6/1/07 as my
end date the query returns several records matching the 5/31/07 criteria.

Could it be a date/time data type format issue? If so what additional
suggestions would you have?

Thanks.
 
O

Ofer Cohen

Do you have the time stored in this field? that will be a reason for no
records to return.

Try
Select * From TableName Where DateValue([ReportDate]) = [Please select
Report Date]

If that not the case, what is the date field type?

--
Good Luck
BS"D


BrianP said:
That is what I tried originally and the query results shows no matching
records. However, when I run the query with the parameter Between [Starting]
and [Ending] and enter for example 5/31/07 as my start date and 6/1/07 as my
end date the query returns several records matching the 5/31/07 criteria.

Could it be a date/time data type format issue? If so what additional
suggestions would you have?

Thanks.

Ofer Cohen said:
The SQL will be

Select * From TableName Where ReportDate = [Please select Report Date]

So, the criteria under the date field name will be [Please select Report
Date], and the user will be prompt with this message.
 
B

BrianP

Yes, the original data comes from a linked sql table. The field there is a
small/date/time data type with no formatting at the table level. Is there a
way to format the query to read the data as a simple short date format then
do a date criteria?

Ofer Cohen said:
Do you have the time stored in this field? that will be a reason for no
records to return.

Try
Select * From TableName Where DateValue([ReportDate]) = [Please select
Report Date]

If that not the case, what is the date field type?

--
Good Luck
BS"D


BrianP said:
That is what I tried originally and the query results shows no matching
records. However, when I run the query with the parameter Between [Starting]
and [Ending] and enter for example 5/31/07 as my start date and 6/1/07 as my
end date the query returns several records matching the 5/31/07 criteria.

Could it be a date/time data type format issue? If so what additional
suggestions would you have?

Thanks.

Ofer Cohen said:
The SQL will be

Select * From TableName Where ReportDate = [Please select Report Date]

So, the criteria under the date field name will be [Please select Report
Date], and the user will be prompt with this message.
 
O

Ofer Cohen

In the previous post I gave an example on how to do it using the DateValue
function

Select * From TableName Where DateValue([ReportDate]) = [Please select
Report Date]

--
Good Luck
BS"D


BrianP said:
Yes, the original data comes from a linked sql table. The field there is a
small/date/time data type with no formatting at the table level. Is there a
way to format the query to read the data as a simple short date format then
do a date criteria?

Ofer Cohen said:
Do you have the time stored in this field? that will be a reason for no
records to return.

Try
Select * From TableName Where DateValue([ReportDate]) = [Please select
Report Date]

If that not the case, what is the date field type?

--
Good Luck
BS"D


BrianP said:
That is what I tried originally and the query results shows no matching
records. However, when I run the query with the parameter Between [Starting]
and [Ending] and enter for example 5/31/07 as my start date and 6/1/07 as my
end date the query returns several records matching the 5/31/07 criteria.

Could it be a date/time data type format issue? If so what additional
suggestions would you have?

Thanks.

:

The SQL will be

Select * From TableName Where ReportDate = [Please select Report Date]

So, the criteria under the date field name will be [Please select Report
Date], and the user will be prompt with this message.
 
T

TC

I am trying to do the same exact thing but with a start and end date. I am
prompted to enter the date but I then get a message saying teh expression is
typed incorrectly or is too complicated. So I then changed the parameter to
enter for just one date and I get the same message.
Here is the code I am using
SELECT penetration.hole, penetration.tag_time, penetration.length,
penetration.rate, DateValue([tag_time]) AS [Date]
FROM penetration
WHERE (((DateValue([tag_time]))=[start (mm/dd/yyyy)]));

The field tag_time is a short date with a time on it. Thank you in advance
for any help.

Ofer Cohen said:
In the previous post I gave an example on how to do it using the DateValue
function

Select * From TableName Where DateValue([ReportDate]) = [Please select
Report Date]

--
Good Luck
BS"D


BrianP said:
Yes, the original data comes from a linked sql table. The field there is a
small/date/time data type with no formatting at the table level. Is there a
way to format the query to read the data as a simple short date format then
do a date criteria?

Ofer Cohen said:
Do you have the time stored in this field? that will be a reason for no
records to return.

Try
Select * From TableName Where DateValue([ReportDate]) = [Please select
Report Date]

If that not the case, what is the date field type?

--
Good Luck
BS"D


:

That is what I tried originally and the query results shows no matching
records. However, when I run the query with the parameter Between [Starting]
and [Ending] and enter for example 5/31/07 as my start date and 6/1/07 as my
end date the query returns several records matching the 5/31/07 criteria.

Could it be a date/time data type format issue? If so what additional
suggestions would you have?

Thanks.

:

The SQL will be

Select * From TableName Where ReportDate = [Please select Report Date]

So, the criteria under the date field name will be [Please select Report
Date], and the user will be prompt with this message.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top