query on iff condition true

[eddii]

i have a query

which has two tables imtemaster and calibrationcertificate i have joined based on imtenumber field of both tables

SQL VIEW

SELECT DISTINCTROW IMTEMASTER.IMTENUMBER, CALIBRATIONCERTIFICATE.IMTENUMBER, CALIBRATIONCERTIFICATE.CALDATE, CALIBRATIONCERTIFICATE.STATUSCER,
FROM IMTEMASTER LEFT JOIN CALIBRATIONCERTIFICATE ON IMTEMASTER.IMTENUMBER = CALIBRATIONCERTIFICATE.IMTENUMBER;

is it possible to get result based on if condition is true

if mfgdate=caldate and field "statuscer" = scrap then it should get the list
if 2011=2011 and statuscer=scrap list shows


if mfgdate<caldate and field "statuscer" = scrap then it should not get in
the list
if 2011<2009 and statuscer=scrap no in list

i tried this in reports but not able to get it is it possible to get by query or vba code is required

 

[Dhamdard]

Easy way would be to view this SQL query in Query Design View. Then in criteria section under the mfgdate field, type the required If condition expression. You can do it to as many fields as you need.

 

[eddii]

thanks for the reply query on iff condition true

thanks for the reply dhamdard
can you just tell me how to write the code for that

this is the condition

if mfgdate<caldate and field "statuscer" = scrap then it should not get in
the list value
if 2011<2009 and statuscer=scrap no in list

 

[MSAccessRookie]

Re: thanks for the reply query on iff condition true

thanks for the reply dhamdard
can you just tell me how to write the code for that

this is the condition

if mfgdate<caldate and field "statuscer" = scrap then it should not get in
the list value
if 2011<2009 and statuscer=scrap no in list

Although I seem to be having a little difficulty fully understanding your requirements, I am fairly sure that what you need is to add an IIf() Statement to your WHERE Clause. The proper format for IIf() is:

IIf({Condition to Test}, {Value if TRUE}, {VALUE if False})
​

Adapt this to your needs and see if it helps.

-- Rookie