FORMAT FUNCTION IN ACCESS QUERY

[ISTodd]

Hi,

I am a self taught novice at access 2003 and need some help formating a function in a query.

I have this function that is testing for certain criteria in my query

1490-2Q%: IIf([split]=-1 And [accounttype]="payroll" And [expr2]="unknown",[withdrawalamount]*0.17,[1490-2a]*[withdrawalamount]/100)

this returns an figure whether the [split] field is -1 or 0 - AND I NEED THE SPLIT TO BE "-1"

so I reworked my expression to the following:

TEST%: IIf([split]=-1,IIf([accounttype]="payroll" And [expr2]="unknown" Or 0,[withdrawalamount]*0.17,[1490-2a]*[withdrawalamount]/100),"")

and i get what I think I need but un formatted (not in currency format


any ideas on where i went wrong? thanks for any help out there...

Todd

 

[Remou]

The first statement appears to be correct. Have you tried testing with just [split]?

[tt]IIf([split]=-1,[withdrawalamount]*0.17,[1490-2a]*[withdrawalamount]/100)
[/tt]

 

[ISTodd]

What if i have multiple criteria?

For example I ONLY want to multiply that field by .17 if ALL the criteria is met.

[ACCOUNTTYPE]=PAYROLL
[SPLIT]=-1
[expr2]=0 or unknown


IF ALL the criteria is NOT met then I want the field to be multipled by [1490-2]

AND FINALLY

if [1490-2] is empty then, " "...

Does this make sense?

Thanks Todd

 

[Remou]

Is this what you mean?

[tt]IIf([split] = -1 And [AccountType] = "payroll" And (expr2 = "unknown" Or expr2 = "0"), [withdrawalamount] * 0.17, IIf(Nz([1490-2], 0) > 0, [1490-2] * [withdrawalamount] / 100, ""))[/tt]