conditional query?

[msalem]

Hi all,

I have a Select query as:

SELECT (qry_1!SET_NO*qry_2![now]-qry_2![now]+1) AS JBOXT FROM qry_LOOPGEN

I want an exception to the usual return as above,

If there is a field Tag_Number in qry_LOOPGEN, can I modify the above query to say

IF Tag_Number=XYZ, THEN JBOXT=ABC ( that is the field JBOXT returns ABC)
ELSE
SELECT (qry_1!SET_NO*qry_2![now]-qry_2![now]+1) AS JBOXT FROM qry_LOOPGEN

Could anyone guide how the if-else query can be used for the above case.

 

[jzwp22]

You might try an IIF() function, but you don't include how qry_1 and qry_2 are joined to qry_LOOPGEN in your FROM clause, so I don't know if the query will work. Also, you should use a period in place of the !

SELECT IIF(Tag_Number=XYZ, "ABC", qry_1.SET_NO*qry_2.[now]-qry_2.[now]+1) AS JBOXT
FROM qry_LOOPGEN