IIF Statement ?

[Guest]

Can someone help with this iif statement in a query? This is an inventory
database with two price scales. Here are my variables:
IIf([Price Code]="Standard Pricing",[Sale Price])
IIf([Price Code]="GM Pricing", [Special Price GM])
IIf([Price Code]="GM Pricing", IIf(And([Special Price GM]=0,[Sale Price])
How would I put that together so that it would cover all bases?

 

[Guest]

Hi, Sharon.

Can someone help with this iif statement in a query?

Try:

SELECT IIF([Price Code]="Standard Pricing",[Sale Price], IIF([Price
Code]="GM Pricing", IIF([Special Price GM] = 0, [Sale Price], [Special Price
GM]))) AS Price
FROM tblPricing;

.. . . where tblPricing is the name of the table and Price is the name of the
column where the appropriate price will be displayed.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

Can someone help with this iif statement in a query? This is an inventory
database with two price scales. Here are my variables:
IIf([Price Code]="Standard Pricing",[Sale Price])
IIf([Price Code]="GM Pricing", [Special Price GM])
IIf([Price Code]="GM Pricing", IIf(And([Special Price GM]=0,[Sale Price])
How would I put that together so that it would cover all bases?

 

[Tom Lake]

Can someone help with this iif statement in a query? This is an inventory
database with two price scales. Here are my variables:
IIf([Price Code]="Standard Pricing",[Sale Price])
IIf([Price Code]="GM Pricing", [Special Price GM])
IIf([Price Code]="GM Pricing", IIf(And([Special Price GM]=0,[Sale Price])
How would I put that together so that it would cover all bases?

I'd create a VBA function instead:

Function Pricing(PriceCode As String, SalePrice As Currency, SpecialPrice As
Currency)
If PriceCode = "Standard Pricing" Then
Pricing = SalePrice
Elseif PriceCode = "GM Pricing" Then
If SpecialPrice = 0 Then
Pricing = SalePrice
Else
Pricing = SpecialPrice
End If
End If
End Function


Tom Lake

 

[BruceM]

From what I can tell you want either [Special Price GM] or [Sale Price] to
appear. With just two options you can use just a single IIf statement.
This should do it:

IIf([Price Code]="GM Pricing" And [Special Price GM] <>0, [Special Price
GM],[Sale Price])

What this is saying is that if the Price Code is "GM Pricing" and Special
Price GM does not equal zero, show Special Price GM, otherwise show Sale
Price.

IIf statements are If ... Then ... Else constructions. You need the Else
(the part after the second comma) in any case. You can nest IIf statements
by inserting another IIf instead of the Then part of the statement:

IIf([Price Code]="Standard Pricing",[Sale Price],IIf([Price Code]="GM
Pricing", [Special Price GM],[Third Option]))

Note that you need an extra parentheses at the end for the second IIf.

As other have suggested, you could do this with VBA. I'm not sure there's a
big advantage one way or the other. With IIf in a query you can bind a
control to that field, which may be simpler in some cases. It depends on
your needs.

 

[Guest]

Thank you so much. I played with that formula for 2 days. It works perfect.

Hi, Sharon.

Can someone help with this iif statement in a query?

Try:

SELECT IIF([Price Code]="Standard Pricing",[Sale Price], IIF([Price
Code]="GM Pricing", IIF([Special Price GM] = 0, [Sale Price], [Special Price
GM]))) AS Price
FROM tblPricing;

. . . where tblPricing is the name of the table and Price is the name of the
column where the appropriate price will be displayed.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

Can someone help with this iif statement in a query? This is an inventory
database with two price scales. Here are my variables:
IIf([Price Code]="Standard Pricing",[Sale Price])
IIf([Price Code]="GM Pricing", [Special Price GM])
IIf([Price Code]="GM Pricing", IIf(And([Special Price GM]=0,[Sale Price])
How would I put that together so that it would cover all bases?