By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,459 Members | 2,284 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,459 IT Pros & Developers. It's quick & easy.

How to fix IIF expression

P: 4
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.
Expand|Select|Wrap|Line Numbers
  1. CONVERT: IIf([UOM]="Milliliter*",[VOLUME]/1000,[VOLUME])
Any suggestions on how to fix?!
May 22 '12 #1

✓ answered by NeoPa

Of course, it may be simpler and more reflective of the logic if you used :
Expand|Select|Wrap|Line Numbers
  1. CONVERT: [Volume] / IIf([UOM] Like 'Millilitre*',1000,1)
As well as reflecting the proper spelling of Millilitre ;-)

Share this Question
Share on Google+
9 Replies


mshmyob
Expert 100+
P: 903
Maybe a typo. Why do you have a caret after Milliliter?

cheers,
May 22 '12 #2

P: 4
Its not a caret, its a * wildcard, in case there is anything after milliliter.
May 22 '12 #3

NeoPa
Expert Mod 15k+
P: 31,398
If it's a wildcard character then you need to use "Like" rather than "=".
May 23 '12 #4

mshmyob
Expert 100+
P: 903
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,
May 23 '12 #5

P: 4
Expand|Select|Wrap|Line Numbers
  1. 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
May 23 '12 #6

mshmyob
Expert 100+
P: 903
That is because there is no statement or expression called [UOM]LIKE"Milliliter*".

Try adding proper spacing like so
Expand|Select|Wrap|Line Numbers
  1. [UOM] LIKE "Milliliter*"
Also I noticed you are missing an opening bracket: ie: you have one "(" and two ")".

cheers,
May 23 '12 #7

NeoPa
Expert Mod 15k+
P: 31,398
Of course, it may be simpler and more reflective of the logic if you used :
Expand|Select|Wrap|Line Numbers
  1. CONVERT: [Volume] / IIf([UOM] Like 'Millilitre*',1000,1)
As well as reflecting the proper spelling of Millilitre ;-)
May 23 '12 #8

P: 4
Halleluia! Thank you NeoPa
May 23 '12 #9

NeoPa
Expert Mod 15k+
P: 31,398
Glad to help Ella :-)

PS. A new discussion was triggered from this thread (Discussion: Spelling of Litre).
May 24 '12 #10

Post your reply

Sign in to post your reply or Sign up for a free account.