IIF Statement in a Select Query, Access 2003

[Akaisar]

Hello,

I know this will probably be a quick question for someone with good knowledge with Access Query's. I am trying to make a query that will calculate fields like this--

Expr1:
IF ([Job Type]="Audit" OR "AuditNTX" THEN [PropAudit]-[Discount Amount])

ELSE

IF ([Job Type="Review" OR "ReviewNTX" THEN [PropReview]-[Discount Amount])

ELSE

IF ([Job Type]="TaxOnly" THEN [TaxOnly]-[Discount Amount])

ELSE

[OtherAmount]-[Discount Amount]


Any ideas how I should write this as an expression in a query? Help would be much appreciated. Thank you in advance--

Akaisar

 

[pbaldy]

I'd be curious what's in the other fields when one of the conditions is met. IOW, what's in PropReview when the type is one of the audit types. If nothing, you could simply have:

(Nz(PropAudit, 0)+Nz(PropReview,0)+Nz(TaxOnly,0)+Nz(OtherAmount,0))-[Discount Amount]

 

[Akaisar]

Basically I have 5 different fields for proposal quote prices-

PropAudit PropReview TaxOnly Other Amount Discount Amount
$1,450 $875 $300 $0 $50

The field [Job Type] is a drop down Combo Box that lists-

Audit
AuditNTX
Review
ReviewNTX
TaxOnly
Correspondance
Other

When I find out what type of Job they want done I select from this menu. What I would like the query to do is automatically fill the expression field by calculating based on what type of service was selected. For example-

If they select a Review-

Job Type Expr1
Review [PropReview]-[Discount Amount], or $825


I am still a beginner with access but I am learning a lot by creating this database and I can see its going to be awesome when it is done. I hope this answers your question, and thank you for all your help!

Akaisar

 

[pbaldy]

What is your table structure (particularly this "transaction" table)? I ask because proper normalized table structure is the foundation that a good application is built on. It sounds like you have all those fields in the table, when there should only be one. I would expect a field to designate the type of job (comparable to the product in a typical sales db) and a field to hold the price for that job. Perhaps another for discount if you want those tracked separately.

I'm not avoiding your original question. The nested IIf() function wouldn't be that hard, though a little tedious to create. Basically the way to learn would be to create each piece on a stand alone basis to get each working, then put one within the other as appropriate. That said, I'd rather make sure you're off on the right foot before moving forward.