Calculations in Query

[jdhaldane]

Hi every one:

I have query based on “Items in shop” table containing a “Client’s price” field for each item. This query calculates several percentages based on the “Client’s price” field.

Second Hand items Shop

This a 5% on the client items price to keep the item at the shop


Ad fee 5%: [Client's price]*0.05

Then I have a form with “Control Source” the mentioned Query with the following fields:

Client’s price:
Ad fee 5%:

The calculation is working perfect, every time you change the value from “Client’s price” calculates the 5% perfectly.

The Issue:

I want the expression in the query to set the percentage to be set to 0% if the “Client’s price” value is under 20. Only calculate the percentage if the “Client’s price” value is over 20.

Thank you very much for your help.

[jzwp11]

You can use an IIF() function in the query.

The general form is as follows:
IIF( expression, true part, false part)


In your case, the function would look something like this:
IIF([Client's price]>20,[Client's price]*0.05,0)

As a general practice, it is best not to have spaces or special characters (',#, @, etc.) in your table of field names.

[jdhaldane]

I’m impressed, it works perfectly well. Thank you very much...

Here comes the next Issue:

In the same Query I have

“Com 10%: [easyAds price]*0.2” – Calculates 20% on the price

I want the expression to calculate 35% on the price if the price is under 20, and to calculate 20% if it’s over 20.

I do appreciate very much your help.

[jzwp11]

You can use the same approach again with the IIF() function:
Something like this:

IIF([easyAds price]<20, [easyAds price]*0.20, [easyAds price]*0.35)


What do you want to happen if [easyAds price]=20? As currently written, the above expression will use 0.35 if the [easyAds price]=20. If that is not what you want then you will have to change the < to <=.

[jdhaldane]

This is fantastic, I'm just starting to get just to write expressions...

Thank's again.

[jzwp11]

You're welcome. Good luck with your database.