"If" statement on Qery?

[DeanRowe]

Hi,

My query produces a “Total” figure. I need to use this figure to generate a “final total” – however it depends upon a field on my form/table as to how this final figure is calculated.

If “text box1” on my form = “Visa” then I need to use the following code:

[Final Total] = [Total] * .98

Or if “text box1” on my form = “Switch” I need to use the following code:

[Final Total] = [Total] – 0.50

Is it possible to perform an “if” statement directly onto a query using design view?

The form that contains “text box1” is called “Order Form” and the table it is stored in is called “Order Table”.

Because I need to perform either a multiplication or a minus calculation I’m a little stumped, any help would be greatly appreciated.

Thank you.

 

[CraigDolphin]

Depends. Are there other possible values that might be selected? If so, what do you want the calculation to be? How do you want the query to handle the situation when nothing is selected in the textbox? (Nulls)

If there are only two possible options then:
FinalTotal: IIF([Forms]![Order Form]![text box1]="Visa",[Total] * 0.98,[Total] - 0.5)

But if there might be other options (typos since this is a text box, or a null)

Something like:

MyFinalTotal: Switch(Nz([Forms]![Order Form]![text box1],"No Value")="Visa",[Total] * 0.98,Nz([Forms]![Order Form]![text box1],"No Value")="Switch",[Total]-0.50,Nz([Forms]![Order Form]![text box1],"No Value") Not In ("Visa","Switch"),"No Result")

...might work better.

 

[DeanRowe]

Hi Craig Dolphin,

Thanks for your response, in all there are 5 options, however the default value is "visa" and it is a required field so it will never be null.
I am looking to set the following:
Visa = *0.98
Mastercard = *.983
Switch = - 0.5
Visa Debit = - 0.3
Misc = * 1 (so it takes nothing off the price)

Thanks again for your help

 

[DeanRowe]

I forgot to mention that the text box is a combo box that has the 5 values (visa,mastercard etc) already entered.

 

[CraigDolphin]

Ok...so the switch function is probably what you need to use then.

FinalTotal: Switch([Forms]![Order Form]![text box1]="Visa",[Total]*0.98,[Forms]![Order Form]![text box1]="Mastercard",[Total]*0.983,[Forms]![Order Form]![text box1]="Switch",[Total]-0.5,[Forms]![Order Form]![text box1]="Visa Debit",[Total]-0.3,[Forms]![Order Form]![text box1]="Misc",[Total])

 

[KeithG]

I would create another table with the Chargetype (VisA, MasterCard,...) and a value field. Then join this table to your query on the cardtype field and then make you caluclation. This way if the values ever change you just update the table.

 

[DeanRowe]

Thank you guys for helping.

I get your point KeithG, and I'm fine setting up the table and a "Value" Field - but how would the code work when you consider that some (like Visa and Mastercard) are multiplications, and others (like Switch) are minus calculations?

Thank you for the code Craig, I'll give that a shot now

 

[CraigDolphin]

The only issue with this is that the operator in the equation changes depending on card type also, Keith.

 

[KeithG]

Check out the example, I put this together quickly just to give you an idea.

 

[CraigDolphin]

Kudos to you Keith Clever solution.

 

[KeithG]

Thank you I always stay away from hardcoding values into an expression becuase it usually leads to headaches down the road.