Urgent DataAdd Function Failing?

[Russ]

I am trying to run a query with the DateAdd Function
nested inside the DateValue function. The field is a date
and time field within a the primary table
called: "tbl_totProd". The query's expression looks like
this Date1: DateValue(DateAdd("h",-7,[dtePostDate])).

If this field is used as a data source for a drop down
box for setting criteria on criteria form for running
reports, simply setting the sort type to:"Ascending"
or "Descending" causes an error: "Data Type Mismatch".
The value set by the criteria setting form is plugged
into controls on the main menu form, which is then pulled
by a Public function from the Main Menu and used as the
criteria for running reports. Here too, I keep getting
the: "Data Type Mismatch" error. So, it may be that
something is falling out or not coming over correctly.

Any suggestion for solving this problem will be
appreciated.

 

[Dale Fye]

Russ,

I'm not sure why you would be getting the Data Type Mismatch, however,
I would be interested in seeing the entire SQL string for this data
source.

Is there only one dtePostDate for any given date? If not, then by
subtracting 7 hours and then converting to a date, you will get
multiples of the date. I'd recommend using a DISTINCT date

SELECT DISTINCT DateValue(DateAdd("h", -7, [dtePostDate])) as Date1
FROM tbl_totProd
ORDER BY DateValue(DateAdd("h", -7, [dtePostDate])) DESC

--
HTH

Dale Fye


I am trying to run a query with the DateAdd Function
nested inside the DateValue function. The field is a date
and time field within a the primary table
called: "tbl_totProd". The query's expression looks like
this Date1: DateValue(DateAdd("h",-7,[dtePostDate])).

If this field is used as a data source for a drop down
box for setting criteria on criteria form for running
reports, simply setting the sort type to:"Ascending"
or "Descending" causes an error: "Data Type Mismatch".
The value set by the criteria setting form is plugged
into controls on the main menu form, which is then pulled
by a Public function from the Main Menu and used as the
criteria for running reports. Here too, I keep getting
the: "Data Type Mismatch" error. So, it may be that
something is falling out or not coming over correctly.

Any suggestion for solving this problem will be
appreciated.

 

[Russ]

Dale:

Thanks, I found the solution. I needed to convert the data
type using CDate at the Global Function level. It now
works fine, I think! If there are further issues, I will
re-post again.

Appreciate all the input...