calculated field with parameter query

[queenie96]

Hi everyone! Please bear with me as I am a new Access user and new to the board. I have been using Excel for some databases (and MrExcel.com) but I am trying to start some databases in Access.

I have created a parameter query. I would like to create a calculated field within this parameter query that calculates totals based on some of the parameters entered.

Lets say one of the parameters is location and is in field titled [Location]. When the query is opened, the user is prompted to enter a location (a, b, or c). I want a calculated field that will look at the location that was entered and then based on that information perform a calculation on another field titled [Dollars]. If the [location] entered is A , I want it to multiply [Dollars] by 17; if the [location] entered is B, I want it to multiply [Dollars] by 23; if the [location] entered is C, I want it to multiply [Dollars] by 45.
What would the code be for this in the criteria line?

 

[bat17]

You would not do it in the criteria but in a new column. In the new column you would look at the value in the Location column and then use in IIF() funtion to do the calculation. If you have quite a few Locations you will find it worth while creating a lookup table and use this rather than an IFF() function as it will be much easier to matain.


Peter