IIF/OR Statement

[mlr0911]

How can I get the IIF statement below to include PDF in the string. Basically what I am trying to do is if the contents of the query = "QDV " or "PDF " add a "-" in front of the string (to make the number negative). I have tried adding an "OR" into the string, however, it makes all my numbers negative. What am I missing?

 IIf([front]<>"QDV  ","-" & Format([cash],"Fixed"),Format([Cash],"Currency"))

As always, Thanks for your help.

 

[pbaldy]

You have <> (not equal to), so logically everything would match an OR statement of "If value isn't equal to A OR isn't equal to B". I suspect you want = instead of <>.

 

[mlr0911]

Even when I changed the <> to a =, it still would give me all negatives. I tried it this way:

CASH_: IIf([front]="QDV  " Or "PDF ","-" & Format([cash],"Fixed"),Format([Cash],"Currency"))

 

[GolfProRM]

CASH_: IIf([front]="QDV  " Or [front] = "PDF ","-" & Format([cash],"Fixed"),Format([Cash],"Currency"))

You have to make the comparison on each side of the OR statement. the above should fix it.

 

[mlr0911]

GolfProRM

Thanks for your reply, I did try that and it is still giving me negative numbers on all my records

 

[GolfProRM]

any chance you could upload a copy of your database so we can look at the system and figure out what's going on?

 

[FoFa]

CASH_: IIf([front]="QDV ","-" & Format([cash],"Fixed"),IIf([front]="PDF ","-" & Format([cash],"Fixed"),Format([Cash],"Currency")))

 

[mlr0911]

Thanks FoFa, your solution worked (I modified it a bit for my needs).

Thanks pbaldy and golfprom for your help as well.

 

[GolfProRM]

Okay, so I'm slightly confused.

Are you trying to make the numbers negative if the file equals QDV or if it doesn't equal QDV?

CASH_: IIf([Front]<>"QDV  " And [Front]<>"PDV  ","-" & Format([cash],"Fixed"),Format([Cash],"Currency"))

Based on the code listed, I changed the OR to an AND, so basically it says if the item is not QDV and it's not PDV, then change it to negative.

 

[pbaldy]

IMHO Ryan's solution is more efficient. You had <> instead of = again.

 

[mlr0911]

This is how I got it to work:

CASH_: IIf([front]="QDV ",Format([cash],"Currency"),IIf([front]="PDV ",Format([Cash],"Currency"),"-" & Format([cash],"Currency")))

 

[GolfProRM]

This works for me also (compared to FoFa's):

CASH_: IIf([Front]="QDV  " Or [Front]="PDV  ","-" & Format([cash],"Fixed"),Format([Cash],"Currency"))

You're referring to PDF, but your code says PDV, so that may be some of the confusion.

EDIT -- that's why the first suggestion didn't work -- you were saying PDF, so since everything in your table was a QDV or a PDF, it was changing them to negative.