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 wrote:
> 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.
>