Doing an "IF" in query Design

[dip757]

Hi,

Can you please tell me how I can do a IF in a query design view.

Basically I have a query as follows:

ColA ColB ColC ColD
12 14
11 10


In ColC: If (ColA-ColB>0, ColA-ColB, 0)
In ColD: If (ColA-ColB<0, ColB-ColA, 0)

Please suggest if there's a simpler alternative

thank you.

 

[Ascentient]

You would use the conditional if statement.

iif(Condition, True, false)

iif(ColA-ColB>0, ColA-ColB, 0)
iif(ColA-ColB<0, ColB-ColA, 0)

 

[dip757]

Thanks ..... was using IFF .... that was it.

but now it's pops up a msg box as below:


Enter parameter value

ColA

blank space
...........


And when i just hit "OK" without any value the result is the CORRECT one i.e. the way I want values in ColC and col D.


Any suggestions on a fix or to suppress this dialogue box by force. (Note I am running the query directly and not using a form or module)

Thanks.

 

[Ascentient]

Typically when that is displayed the field you are trying to pull a value from is not available, does not exist, or is not referenced correctly.

Make sure the field names are accurate. Since you are not using a form or module there should not be to much to check.

If you are using more than one table in your query make sure the table references are correct.

Let me know what happens.