Complex Update

[dbprogman]

I need to import information from an XML file that includes a wide array or codes. Each code stipulates which amount is entered into a particular field.

The importation and conversion is not a problem this has been achieved successfully, however the problem exist in the update procedure. Due to the extensive amount of different codes (est. 30) an IIF statement becomes to large and complex to evaluate.

The following statement though being very incorrect it does show an example of what is being attempted.

IIf([ClaimUpdate]![claimtype]="com1" Or "com2",[tblClaims]![CommencementCIAmount],"") Or IIf([ClaimUpdate]![claimtype]="RCM*",[tblClaims]![ReCommencementCIAmount],"") Or IIf([ClaimUpdate]![claimtype]="SCH1",[tblClaims]![SchoolBasedCommAmount],"")

Basically if table [claimupdate]![claimtype] = "com1" then [tblClaims]![Related Field] = amount else leave blank.

Question is there a better way to construct the criteria or should a module or procedure be used instead to evaluate? Or is there a way to construct a nested IIF statement to evaluate a large complex criteria?

Any suggestions greatfully recieved...

If it looks like a problem, acts like a problem it must be a problem!

 

[neileg]

Not sure of the context here but you may find that a Case statement will help.