CODEWITHSUNDEEP
sqlms-accessiif
yope
yope

Reputation: 13

SQl Use IIF to compare 2 values in same row

I am using SQL in MS Access and need to compare 2 values in a row to determine the result of a 3rd field. The "Yearmove" field does not have a value in all cases. Here is the test code I am running

SELECT z.SCHOOL, z.Year as Year,  ProgBuild.BldgNo, ProgMoveBuild.yearmove AS Yearmove,
IIF([Year]=[Yearmove], 1, 0) AS Result

The results are fine in the cases where "Yearmove" is blank, and show 0 as expected. Anyplace "Yearmove" has a number the Result field reads "#ERROR", so both when the condition is met and when it is not. I have tried populating the "YEAR" field with both text (e.g. "2014") and non-text (e.g. 2014) but get the same result. All data is numeric. Here is what a section of the results look like: 

SCHOOL  Year    BldgNo  Yearmove    Result
254     2014     254                   0
256     2014     256                   0
260     2014     260                   0
261     2014     261                   0
262     2014     202                   0
301     2014     301                   0
307     2014     307     2019        #ERROR
313     2014     313     2019        #ERROR
314     2014     314                   0
321     2014     321                   0
322     2014     322                   0

Upvotes: 1

Views: 2418

Answers (1)

HansUp
HansUp

Reputation: 97100

This query throws an error, "Data type mismatch in criteria expression":

SELECT IIf('2014'=2014, 1, 0)

So I suspect your Year and yearmove fields are different datatypes: one is numeric; and the other is text.

Find out what datatypes you're dealing with ...

SELECT
    z.Year,
    TypeName(z.Year) AS TypeOfYear,
    ProgMoveBuild.yearmove,
    TypeName(ProgMoveBuild.yearmove) AS TypeOfyearmove
FROM [whatever you have now as your data source(s)]

If changing the datatype of one of those fields not practical, you can cast a field's values to another type for the IIf condition in your query. CStr() will cast a numeric value to a string. And Val() will cast a string value to a number. However, either of those functions will complain when you give them Nulls. So you can use Nz() to substitute something else for Null before you feed the value to CStr() or Val().

Upvotes: 1

Related Questions