Multiple IIF In Query

M

Mark Gregory

I'm sure there is a simple solution to this, I have tried some other solututions to no effect.

I have a query field [MemebrType] where the data will only be Gold, Silver or Bronze.

I need another field [Cost] in the query that is dependant on the three conditions.

If [MemebrType] = Gold then [Cost] = ?380

If [MemebrType] = Silver then [Cost] = ?300

If [MemebrType] = Bronze then [Cost] = ?250

I presume it would start

Cost:IIf([MemberType]="Gold","380".....etc

I have tried many combinations to no avail, please help!!


I

EggHeadCafe - Software Developer Portal of Choice
BOOK REVIEW: Silverlight 2 Unleashed / Bugnion [SAMS]
http://www.eggheadcafe.com/tutorial...f9b-d99c0d78cae8/book-review-silverlight.aspx
 
M

Mark Gregory

It should be MemberType obviously - sorry



Mark Gregory wrote:

Multiple IIF In Query
09-Nov-09

I'm sure there is a simple solution to this, I have tried some other solututions to no effect.

I have a query field [MemebrType] where the data will only be Gold, Silver or Bronze.

I need another field [Cost] in the query that is dependant on the three conditions.

If [MemebrType] = Gold then [Cost] = ?380

If [MemebrType] = Silver then [Cost] = ?300

If [MemebrType] = Bronze then [Cost] = ?250

I presume it would start

Cost:IIf([MemberType]="Gold","380".....etc

I have tried many combinations to no avail, please help!!


I

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
Generic GetXmlReader Data Access Method
http://www.eggheadcafe.com/tutorial...d-b6ebb3575e5e/generic-getxmlreader-data.aspx
 
J

John W. Vinson

I'm sure there is a simple solution to this, I have tried some other solututions to no effect.

I have a query field [MemebrType] where the data will only be Gold, Silver or Bronze.

I need another field [Cost] in the query that is dependant on the three conditions.

If [MemebrType] = Gold then [Cost] = ?380

If [MemebrType] = Silver then [Cost] = ?300

If [MemebrType] = Bronze then [Cost] = ?250

I presume it would start

Cost:IIf([MemberType]="Gold","380".....etc

I have tried many combinations to no avail, please help!!
Click to expand...

You could use multiple IIF() function calls, or you could use the Switch
function, or you could use VBA code (the above looks like VBA but it is not
valid in a query)...

or you could use Access as designed, as a relational database, and create a
two field table MemberTypes, with three rows:

"Gold"; 380
"Silver"; 300
"Bronze"; 250

and simply join this to your members table.

This would have the advantage that if your cost levels change (with inflation,
say) you could simply edit a record in the table rather than digging through
all your code trying to find the arguments to deeply nested IIF functions.
 
J

John W. Vinson

It should be MemberType obviously - sorry
Click to expand...

It was a MemberTypo!

See answer in the other thread. The best solution is not to use IIF or any
function call at all.
 
Top