MS Access Data type mismatch in expression only in where statement

0
votes

I'm working on an expression that throws a data type mismatch only when in the Where statement. 

Current: IIf(Nz([ISRS_VAL_ST_CAPP_LVL].[NBR_YRS_VALID],99)>0,
IIf(DateAdd("yyyy",CInt(Nz([ISRS_VAL_ST_CAPP_LVL].[NBR_YRS_VALID],99))
,CVDate(Format(Nz([TEST_DATE],19891231),"0000-00-00")))>Now(),True,False),True)

As you can see, I've added an Nz() function to every reference and even tried to cast CBool() on the whole statement, but it still throws the data type mismatch.

I've pinpointed it down to this expression and the expression works exactly as expected in Select statement. When I add it to the Where statement looking to return only True, the error occurs.

What could I be missing?

Update: full query for those interested

SELECT Student.TECH_ID, Student.CAPP_LVL, Values.ABBR, Values.SHORT_DESC, Values.LONG_DESC, Values.GROUP_NBR, Values.NBR_YRS_VALID, Student.SEQ_NBR, Student.CAPP_LVL_SCORE, Student.TEST_DATE, IIf(Nz([Values].[NBR_YRS_VALID],99)>0,IIf(DateAdd("yyyy",CInt([Values].[NBR_YRS_VALID]),CVDate(Format([TEST_DATE],"0000-00-00")))>Now(),True,False),True) AS [Current], Values.END_DATE, Student.LOAD_DATE
FROM Student INNER JOIN Values ON Student.CAPP_LVL = Values.CAPP_LVL
WHERE (((Values.ABBR)<>"MTHB") AND ((Values.END_DATE) Like "9999*"))
ORDER BY Student.TECH_ID, Student.CAPP_LVL, Student.SEQ_NBR;
3
sqlms-access
Could you please share the full query? - user8128167

3 Answers

2
votes

What data type and format is TEST_DATE? I get an error if I make it a Date/Time datatype and make the data evaluate to false, but it works if it evaluates to true. That may be why you see it working in a Select. When i make TEST_DATE a double, and use your format in the nz function (19981231) it works in all cases.

0
votes

A Where statement doesn't look like a Select statement. Access is expecting a conditional expression that it can test for true or false.

The quick version would be take that whole statement and test it for True:

WHERE (IIf(Nz([ISRS_VAL_ST_CAPP_LVL].[NBR_YRS_VALID],99)>0,
IIf(DateAdd("yyyy",CInt(Nz([ISRS_VAL_ST_CAPP_LVL].[NBR_YRS_VALID],99))
,CVDate(Format(Nz([TEST_DATE],19891231),"0000-00-00")))>Now(),True,False),True))
= True

A better solution would be to rewrite it as a few different conditional statements joined by AND or OR. I'm having trouble parsing it, so I don't think I'm going to try my hand at that.

0
votes

This works for me:

SELECT 
    IIf(Nz([ISRS_VAL_ST_CAPP_LVL].[NBR_YRS_VALID],99)>0,IIf(DateAdd("yyyy",
       CInt(Nz([ISRS_VAL_ST_CAPP_LVL].[NBR_YRS_VALID],99)),
       CVDate(Format(Nz([TEST_DATE],19891231),"0000-00-00")))
         >Now(),True,False),True) 
       AS [Current],
    ISRS_VAL_ST_CAPP_LVL.NBR_YRS_VALID, 
    ISRS_VAL_ST_CAPP_LVL.TEST_DATE
FROM ISRS_VAL_ST_CAPP_LVL
WHERE (((IIf(Nz([ISRS_VAL_ST_CAPP_LVL].[NBR_YRS_VALID],99)>0,IIf(DateAdd("yyyy",
    CInt(Nz([ISRS_VAL_ST_CAPP_LVL].[NBR_YRS_VALID],99)),
    CVDate(Format(Nz([TEST_DATE],19891231),"0000-00-00")))
       >Now(),True,False),True))=true));

Both NBR_YRS_VALID and TEST_DATE are Long Integer