Software

Ask something
Discussion Locked

Access 97 IIF Function Problem

0 helpful
7 replies

by chainsawz

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a query set up with IIF functions on currency fields. Example:

Denied Amount: IIf([CLAIM STATUS]="denied" Or Left([CLAIM STATUS],3)="rej",[ADJUSTED AMOUNT],"")

If the status in the CLAIM STATUS field is equal to denied or rej, then I want the dollars in the ADJUSTED AMOUNT field to be present AS Denied Amount. This works great, until I build the next query and try to sum. I get the error 'The Microsoft database engine could not execute the SQL statement because it contains a field that has an invalid data type". Well, I made sure and set the properties of the alias field 'Denied Amount' to currency, and it still won't work. Does the IIF function convert everything to text, so that I will never be able to do any further calculations on the data? Anyway around this?

7 replies

by mahlong

Your IIF statement is creating 1 field ie. Denied Amount.

If you use "" in the last condition, you are changing it to text, because 1 field cannot be text and value at the same time.

try using 0

Denied Amount: IIf([CLAIM STATUS]="denied" OrLeft([CLAIM STATUS],3)="rej",[ADJUSTED AMOUNT],0)

hope this helps you.

by sl-campbell

In the first query you might change the field to numeric and change the last part of the IIF to:

....",[ADJUSTED AMOUNT],0)

Or, in your sum query try this:

Sum(Val([ADJUSTED AMOUNT])) As Totals

The Val tries to interpet a string, character per character, as a number, and if it cannot because it has come across a non numberic character, it will return 0, or the numbers up to the invalid character: "Test" return 0, but, "123Test" returns 123, and , "Test123" returns also 0.

If this doesn't work in a query, then I would suggest create the second Sum query based off of the same source table as the first query, and use the IIF in the Sum query as in (added only Sum([ADJUSTED AMOUNT]):

Denied Amount: IIf([CLAIM STATUS]="denied" Or Left([CLAIM STATUS],3)="rej",Sum([ADJUSTED AMOUNT]),"")

Note: The Val Function will not work as expected in some countries which use a comma as the decimal seperator (it will return whole numbers only), unless you are dealing with whole numbers only. In those cases you will need to do some special, additional formating.

by sl-campbell

To malonG:
When I started answering this question, there were no posts.
I guess you posted this right before me. Sorry.

This discussion is closed to new replies.