Calculated field in Query -need help

[rogers5293]

I have several calculated fields in a query that work fine. I have two that have conditions on them and was wondering if you CAN'T have IF statements in a calculated field query. If not, how do I do this?

I have two tables. One is a product table and one is a rates table. If the calcuated field has less than 2.00 as the value then 2.00 needs to be defaulted as the value. (If it is less than $2.00 they want to charge a minimum fee)

cFeeCalc: [TableName].[NbrofProducts] * [Rates].[DistFee]
^^this works fine but if it is less than 2.00 I need to default the vaule to 2.00

cFeeCalc: IFF([TableName].[NbrofProducts] * [Rates].[DistFee] < 2.00,2.00,[TableName].[NbrofProducts] * [Rates].[DistFee])
^^this does not work at all but is what I need
-------------------------------------------------
The other calculated field needs to determine if a value from one table is "L" then use a fee from the rates table but if the value is "S' then use a different fee from the rates table.

Logic: IFf([TableName].EnvelopeSize = "L",[TableName].[EnvelopeNbr] * [Rates].[LargeEnvFee],[TableName].[EnvelopeNbr] *[Rates].[SmallEnvFee])

I would appreciate any help!!!!!! I am stuck!!

 

[plog]

For starters, in both your queries 'IFF' should be 'IIF'. If that doesn't fix the issues post some samples of records that it isn't working for.

 

[jzwp22]

First, you need an immediate if function which is IIF() not IFF().

The next issue would be where are the fields coming from i.e. from what tables

[TableName].[NbrofProducts]
[TableName].EnvelopeSize
[TableName].[EnvelopeNbr]
all rate fields

Do the [TableName] references above all refer to the same table?

The other thing I noticed is that you have multiple rate fields. I think the different types of rates should be records in the rate table not separate fields.

tblRates
-pkRateID primary key, autonumber
-txtRateName
-RateValue

So the data in the table might look like this

pkRateID|txtRateName|RateValue
1|DistRate|some value
2|LargeEnvFee|some value
3|SmallEnvFee|some value

I am guessing that the rate table is not part of your query (i.e. not in the FROM clause). If that is the case, you will have to use the DLookup() function to get the appropriate rate.

Providing more detail on your table structure might be helpful.

 

[rogers5293]

Thanks so much for taking the time to reply! It is really appreciated!!

I tired the IIF (duh!) on a simple expression and that worked but I am still having problems with the complicated one:

cASADistFee: IIf([Billing].[NbrofSHPos]*[MFRates].[MFMaterialDistFee]<2,2,[Billing].[NbrofSHPos]*[MFRates].[MFMaterialDistFee])


Yes, in normal form a Rates table would be as you suggested but I have to keep a rates table lilke this:

Rate ID
Rate MatFee
RateEnvFee
RateDistFee
...about 5 more fees

I am importing historical data that had these fields in a spreadsheet and from time to time they would change all the rates and would just change the formula in the table cells. When we go live I am going to have them put the rate id on each row that compares to which rate id they need for the WHOLE row.

 

[rogers5293]

I GOT IT WORKING!!!! Yeah! Thanks SOOOO much! (From the last post I realized I had a table name mispelled in the calculation.

Thanks again for responding so fast!

 

[jzwp22]

Glad you got it worked out! Good luck with your project.