Date/Time Expression with Parameter Query

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

Guest

I am having trouble quering on a table that contains a Date/Time field. I needed to exclude the "Time" from a Date/Time field and then use a parameter query to extract the date data for a report.

Following Knowledge Base Article 149945 I added the expression Expr1: CVDate(Int([DateTimeField])) to my query. This worked fine in the query table but when I run the parameter query "Between [StartDate] And [EndDate]" I get zero records

Any Ideas..
Thanks.
 
Ed,

Instead of the CVDate method, I prefer the DateSerial(Year
(YourDateField),Month(YourDateField),Day(YourDateField))
method. When adding Group By to the Query though, I
suggest adding a column for Where YourDateField >=
Datevalue([StartDate]) AND < (Datevalue([EndDate])+1).

In my experience, this works better then the between
statement and eliminates the problems of missed records
because the time component on the date. Plus, if you're
using a form for the user to enter the dates, you've
already made the conversion to a date value.

Have a great day.

Mark
-----Original Message-----
I am having trouble quering on a table that contains a
Click to expand...
Date/Time field. I needed to exclude the "Time" from a
Date/Time field and then use a parameter query to extract
the date data for a report.
Following Knowledge Base Article 149945 I added the
Click to expand...
expression Expr1: CVDate(Int([DateTimeField])) to my
query. This worked fine in the query table but when I run
the parameter query "Between [StartDate] And [EndDate]" I
get zero records.
 
Mark
I tried your suggestion and the DateSerial method works but I can't figure out how or where to use the WHERE YOUR DATEFIELD >= DATEVALUE info

----- Mark wrote: ----

Ed

Instead of the CVDate method, I prefer the DateSerial(Yea
(YourDateField),Month(YourDateField),Day(YourDateField))
method. When adding Group By to the Query though, I
suggest adding a column for Where YourDateField >=
Datevalue([StartDate]) AND < (Datevalue([EndDate])+1)

In my experience, this works better then the between
statement and eliminates the problems of missed records
because the time component on the date. Plus, if you're
using a form for the user to enter the dates, you've
already made the conversion to a date value

Have a great day

Mar
-----Original Message----
I am having trouble quering on a table that contains a
Click to expand...
Date/Time field. I needed to exclude the "Time" from a
Date/Time field and then use a parameter query to extract
the date data for a report.expression Expr1: CVDate(Int([DateTimeField])) to my
query. This worked fine in the query table but when I run
the parameter query "Between [StartDate] And [EndDate]" I
get zero records
 
Back
Top