Urgent DataAdd Function Failing?

  • Thread starter Thread starter Russ
  • Start date Start date
R

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.
 
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.
 
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...
 
Back
Top