Data type mismatch in criteria expression issue

[Benjamins via AccessMonster.com]

Hi,

I have a query call ListRecur where it will Caculate the date when the entry
is going to recur

Below it the query Field information
DetId (FieldName same as table, Long Integer)
DetDate (FieldName same as table, Date/Time)
RecurDate (A expression Where it will add the DetDate base on user selection
of recur period) Formula: DateAdd([DetRecurType],[DetRecurPeriod],[DetDate])

[DetRecurType] ia a string containing either 1 of the following the following
: 'd', 'ww','m','yyyy'
[DetRecurPeriod] is an integer

This query works fine and the RecurDate can be caculate correctly.

Later i try to add the criteria to RecurDate so that it will filter out any
records that
is < Now() which i have been using for the rest of the other queries.
It give me an error saying that Data type mismatch in criteria expression

Is there any thing that i have done wrong that the system does not recongise
that the RecurDate is not a date format.

 

[John Spencer]

Well, you will have a problem if you have a null value in any record in the
field DetRecurType or in the field DetRecurPeriod. So you might want to test
for that. Also, if DetRecurType is not one of the valid arguments, you will
get an error.

As a guess you have a data problem somewhere. You can probably handle the
problem by using something like the following to do the calculation:

IIF(IsNumeric(DetRecurPeriod) and DetRecurType IN ("d","ww","m","yyyy"),
DateAdd([DetRecurType],[DetRecurPeriod],[DetDate]),Null)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Benjamins via AccessMonster.com]

Thanks for your info. I will give it a try.

Well, you will have a problem if you have a null value in any record in the
field DetRecurType or in the field DetRecurPeriod. So you might want to test
for that. Also, if DetRecurType is not one of the valid arguments, you will
get an error.

As a guess you have a data problem somewhere. You can probably handle the
problem by using something like the following to do the calculation:

IIF(IsNumeric(DetRecurPeriod) and DetRecurType IN ("d","ww","m","yyyy"),
DateAdd([DetRecurType],[DetRecurPeriod],[DetDate]),Null)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hi,

[quoted text clipped - 20 lines]

Is there any thing that i have done wrong that the system does not recongise
that the RecurDate is not a date format.