Date() criteria data mismatch

[amberella]

Good Afternoon,

Long time lurker, first time poster...

I have a query where I used the expression builder to determine an inspection date based on 10 months after a permit date, but also taking into account whether there is an extension or not. My expression goes something like this:

IIf([RFA MAIN].[PermitExtension]="3 Months",DateAdd("m",13,DateSerial(Year([rfa main].[DATE RET'D TO CUST]),Month([rfa main].[DATE RET'D TO CUST])+1,0))...

and it repeats looking for the different extension times (which is a text field). The inspection date is always the last of the month (i.e. permit date 1/7/18, inspection date is 10 months out but the last day of the month so 10/31/18).
.
The expression field gives me the results I need, but when I try to apply <Date() as my query criteria, it gives me a data mismatch error. I've tried format as "mm/dd/yyyy", formatDateTime and DateValue and none of them have solved my issue?

Any suggestions?

Thanks!

 

[plog]

Can you post your full SQL?

 

[June7]

Seems the expression result should be valid date for comparison. What is rest of the calc? Try wrapping with CDate() and see if that helps.

 

[amberella]

Full expression is:

Insp Date: CDate(IIf([RFA MAIN].[PermitExtension]="3 Months",DateAdd("m",13,DateSerial(Year([rfa main].[DATE RET'D TO CUST]),Month([rfa main].[DATE RET'D TO CUST])+1,0)),IIf([RFA MAIN].[PermitExtension]="6 months",DateAdd("m",16,DateSerial(Year([rfa main].[DATE RET'D TO CUST]),Month([rfa main].[DATE RET'D TO CUST])+1,0)),IIf([RFA MAIN].[PermitExtension]="9 Months",DateAdd("m",19,DateSerial(Month([rfa main].[DATE RET'D TO CUST]),Month([rfa main].[DATE RET'D TO CUST])+1,0)),IIf([rfa main].[PermitExtension]="12 Months",DateAdd("m",22,DateSerial(Year([rfa main].[DATE RET'D TO CUST]),Month([rfa main].[DATE RET'D TO CUST])+1,0)),DateAdd("m",10,DateSerial(Year([rfa main].[DATE RET'D TO CUST]),Month([rfa main].[DATE RET'D TO CUST])+1,0)))))))

As you can see, I tried the CDate(), but still got the same data mismatch error.

 

[June7]

Consider:

Insp Date: DateAdd("m", Switch([PermitExtension]="3 Months",13, [PermitExtension]="6 months",16,
[PermitExtension]="9 months",19, [PermitExtension]="12 Months",22, True,10), DateSerial(Year([DATE RET'D TO CUST]),Month([DATE RET'D TO CUST])+1,0))

Or:
Insp Date: DateAdd("m", Val(Nz([PermitExtension], 0)) + 10, DateSerial(Year([DATE RET'D TO CUST]),Month([DATE RET'D TO CUST])+1,0))

The [PermitExtension] field really should be number type.

 

[MarkK]

Why would you ever store a quantity of months as a string like this....

"3 months"
' or
"12 months"

?????

Imagine how much simpler it would be if you store [RFA MAIN].[PermitExtension] as a number, and then just use that numeric value to do the math....

Insp Date: DateAdd("m",10 + [RFA MAIN].[PermitExtension], DateSerial(Year([rfa main].[DATE RET'D TO CUST]),Month([rfa main].[DATE RET'D TO CUST])+1,0))

hth
Mark