asked on

Issue with Microsoft Access Query

I have a query that needs to multiply a dollar amount by a percentage. However, that percent is based on the Decision Package, which is another field in the query. If the Decision Package is 1, it must be multiplied by a certain percentage. If it is not 1, it needs to be multipled by a different percentage. I checked my query and it is not reading my if correctly. This is the field and how I wrote it. I tried it with and without the single quote around the Decision_Package_Code

HELP! Automatic A and 500 points

DU1RatioBreak: IIf([Decision_Package_Code]='1',([JournalAmount]*[DU1Percentage]),([JournalAmount]*[DU1Percentage_NB]))

Kevin Cross

This should work. If it doesn't please list error you are getting. This assumes you have the columns [DU1Percentage] and [DU1Percentage_NB] in table defined as numerical datatype along with [JournalAmount].

DU1RatioBreak: [JournalAmount]*IIf([Decision_Package_Code]=1,[DU1Percentage],[DU1Percentage_NB])

Open in new window

christiemhjohnson

ASKER

It doesn't give me an error, but it still doesn't read the if correctly.

ldunscombe

Are you sure that all of your calculation fields ie [JournalAmount],[DU1Percentage] and [DU1Percentage_NB] are all numeric, You also need to determine for sure if your [Decision_Package_Code] is text or numeric.

If All are numeric data types then your code (as is) without the quotes should work. ie

DU1RatioBreak: IIf([Decision_Package_Code]=1,([JournalAmount]*[DU1Percentage]),([JournalAmount]*[DU1Percentage_NB]))

Leigh

christiemhjohnson

ASKER

When i put Decision_Package_Code in " ", I get an error.

The other fields are fields that I have created as variables in other queries, how do I know the data type?

ASKER CERTIFIED SOLUTION

ldunscombe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kevin Cross

If you cannot change or you are creating these dynamically in your query, then you can convert them for sure in this formula.

DU1RatioBreak: CDbl([JournalAmount])*CDbl(IIf([Decision_Package_Code]=1,[DU1Percentage],[DU1Percentage_NB]))

Open in new window