Help with IIF Function

[SteveE]

Just wonder if anyone could give me some help on an IIF function used in my query:

I have a table which has numerous “W” codes, the charging for an order does not occur if it has a W12 or a W14 code and has no “[Authorising Name]” BUT does however charge if the weight of the order is over 100kg


IIf([WCode]="W12" Or [WCode]="W14",IIf([Authorising Name]="",0,[charge], IFF ([weight]>99,[charge],0)))

I can get this working for a single W code but not for the 2 OR I can get it working for both Codes but then the weight side doesn’t.

I have tried all combinations of <> = < etc etc but still no luck., I think I need another OR but no matter where I put it I hit problems either with the function or the result.

Another small issue, my [authorising Name] field can appear to be empty, but in the field are spaces what is the best way for ensuring I capture both empty and theses space records ?

Thanks in advance

 

[SMatthews]

I think this should work.

IIf(([WCode]="W12" Or [WCode]="W14"),IIf([Authorising Name]="",IIF([weight]>99,[charge],0),[charge]), 0)

 

[SteveE]

IIf Problem

Thanks for the reply, I tried it and result = too many arguments, I played around with it but still to no avail.

thanks anyway

 

[SMatthews]

Try this:

IIf([WCode]="w12" Or "w14",IIf([Authorising Name] Is Null,IIf([Weight]>99,[charge],0),[charge]),0)

 

[SteveE]

IIF Problem

Thanks very much that has resolved the IFF issue.

My only other problem is with the Authorsed Name field, all the data in the tables are imported from our main unix server the field if not containg data should be empty but in many of them there are spaces this causes "" or is null to miss them, I can confirm this by deleting the spaces in a "blank" cell nad the iff statement then works, any ideas on just how I can enterint the iff statement [Authorised Name] is null or " "

thanks again