Date Parameter Values

[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.

 

[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.

 

[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.

 

[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

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.

 

[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?

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.

 

[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

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?

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.

 

[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.

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

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?

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.