Date Issue

[Syd]

I have this application in which I roll back the 7 hours
after 12 mid night back into the previous day's shift date
values using the query function that follows.

dteDate1: DateValue(DateAdd("h",-7,[dteDate]))

Previously, I used the value of the date column to filter
returned data in reports. The date field had been changed
to a short date format meaning date and time. When I use
the above query function I get a "Data Type Mismatch In
Criteria" Error.

Any ideas for a work around or Data conversion will be
welcome.

Assigning this new value to a function that is
passed from the active form to the (live) Main Menu form
and called to filter the records returned when reports are
run does not work. Fore example the range may be from June
01, 2003 to August 31, 2003. But the returned records rune
from some time in April to the last known record created
yesterday.

Somehow, the function does not see the value in the Main
Menu control as a Date value that is passed back to
reports underlying query criteria. Any assitance will be
appreciated.

Thanks,

 

[John Spencer (MVP)]

If you mean you've used the FORMAT function to create dteDate then dteDate then
the "date" being returned is a STRING.

You might try:
DateAdd("h",-7,DateValue([dteDate]))

Also, I think that if dteDate is null or if it is a zero-length string, that
datevalue will return errors - either Run-time error 94 - Invalid use of null OR
Run-time error 13 - type mismatch.