IIf/and statement

[BringTheR41n]

Im working in a query and i need to set up an IIF statement. So far i have "=IIF("Clear"="c","0")else(=IF("Acct"="m","AmountP aid","0")) but i dont know what to do from here. I need the function to to go through 2 different sources before it will display a value. if clear=c then it will leave it blank. but if clear does NOT=c then it looks at the account. if the account equal m then the value will be in that column. I dont usually work in queries and im trying to learn how. Any help is appreciated.

[ranman256]

The ELSE is implied you dont type it..

IIF( test, result if TRUE, result if FALSE)

[ranman256]

If you are making a really complex IF...you can write your own function to simplify the field.
Using modules and code..example:

Public Function getLOB(ByVal pvWA, ByVal pvCaseCat, ByVal pvSub)
Select Case True
   Case pvWA = "WA"
      getLOB = "W1A"
   Case Right(pvSub, 1) = "S"
      getLOB = "S1N"
   Case pvSub = "CMB"
      getLOB = "CMB"
   Case pvCaseCat = "REC"
      getLOB = "REC"
   Case pvCaseCat = "INS"
      getLOB = "INS"
   Case Else
      getLOB = "TCC"
End Select

[BringTheR41n]

I am not experienced enough to work with complex functions, but to keep it simple it should look something like"=IIF([Clear]="c",'0',IIF([Acct]="m",[AmountPaid],'0'))" right? the 'clear' and 'Acct' are the actual names im using. I know that i set up the IIf statement by"=IIF(statement,true,false)" but i dont know when to include the second iif part of the function.

[ranman256]

Your statment, "IIF([Clear]="c",'0',[Acct]="m",[AmountPaid],'0'))"

says if clear = c, reply 0 ,
else
if [acct] = m then
[amtpaid]
else
0

(no need for equals, KEEP the second iiF)

[BringTheR41n]

I plugged in this formula, " IIf([Clear]='c',"0",(IIf([Acct]='m',[AmountPaid])))" and it does what i need it to, except with the [AmountPaid] it needs to be a currency format. it is currently outputting the data as a text. I have the table columns it is pulling from/placing set in currency and yet it's still sends text format. Any ideas why?

[BringTheR41n]

I plugged in a formula, " IIf([Clear]='c',"0",(IIf([Acct]='m',[AmountPaid]))) " in part of a query to where it looks at 2 different columns and determines what info to put in the 3rd. i need the 3rd column to read info where if one column is 'c' it does nothing, but if there isnt a 'c' it needs to look at a second column and determine if it has an 'm', if it does it needs to go to another column and put the [AmountPaid] currency and place it in the 3rd column. the problem is, when i run the formula it takes the data i need but places it in text format. i need it to be currency. Any ideas? Thanks a bunch

[John_G]

Hi -

Try taking the whole expression and putting it inside a Format function:

Format(expression,"currency")

John

[BringTheR41n]

Format(NotCleared-US: IIf([Clear]='c',"0",IIf([Acct]='u',[AmountPaid])),"currency")
i tried the above formula and it says the comma is an invalid syntax, but when i delete, it says the currency is an invalid syntax. i also tried;
Format(NotCleared-US: IIf([Clear]='c',"0",IIf([Acct]='u',[AmountPaid]))
But when i try that and change the format in the property sheet, it still has the format in text..is there something im not doing correctly? Thanks John

[June7]

Try:

NotCleared-US: Format(IIf([Clear]="c", 0, IIf([Acct]="u", [AmountPaid], 0)), "currency")

[BringTheR41n]

Awesome. It worked. thank you very much