How to fix IIF expression

[Ella Twary]

I'm trying to sum volumes in a table, however I have more than one unit of measure (liters and milliliters). I created the following in the 2007 Access Expression Builder, but when I run the query, the /1000 (to take mL to L) does not calculate.

CONVERT: IIf([UOM]="Milliliter*",[VOLUME]/1000,[VOLUME])

Any suggestions on how to fix?!

[mshmyob]

Maybe a typo. Why do you have a caret after Milliliter?

cheers,

[Ella Twary]

Its not a caret, its a * wildcard, in case there is anything after milliliter.

[NeoPa]

If it's a wildcard character then you need to use "Like" rather than "=".

[mshmyob]

Then like Neo says you cannot use an equal sign. The qual sign will look for a literal match and by your answer you will never find a record in your table where the column UOM="Milliliter *".

Change the equal sign to the word LIKE as indicated by Neo and see if that helps you.

cheers,

[Ella Twary]

CONVERT: IIF ([UOM]LIKE"Milliliter*",[VOLUME])/1000,[VOLUME])

I'm getting an error stating that the expression contains invalid syntax, or you need to enclose your text data in quotes.
Now what?

Thanks for your help mshmyob and Neo

[mshmyob]

That is because there is no statement or expression called [UOM]LIKE"Milliliter *".

Try adding proper spacing like so

[UOM] LIKE "Milliliter*"

Also I noticed you are missing an opening bracket: ie: you have one "(" and two ")".

cheers,

[NeoPa]

How to fix IIF expression

Of course, it may be simpler and more reflective of the logic if you used :

CONVERT: [Volume] / IIf([UOM] Like 'Millilitre*',1000,1)

As well as reflecting the proper spelling of Millilitre ;-)

[Ella Twary]

Halleluia! Thank you NeoPa

[NeoPa]

Glad to help Ella :-)

PS. A new discussion was triggered from this thread (Discussion: Spelling of Litre).