IIF statement not working

[Jim L.]

I am working on a fuel inventory report. I have 11 tanks that may be either
unleaded, premium or diesel, and may change throughout the year. The product
in each tank, along with the amount, is entered on a form, and this report is
based on the same table. I would like to have a total amount of each product
shown on the report. I have made a text box with the following expression in
the control source;

iif([tank1product]="Unleaded",[tank1gallons],0)

I believe this should display the amount of gallons in tank 1 in the text
box if the product is unleaded, and display a 0 if not. From here, I would
do the same for each tank and each product, then add another text box under
these to total each product. None of these boxes would be visible on the
report except the product totals.

Unfortunately, I am getting a syntax error about commas and/or parentheses
when I try the first box. I am probably going about this the hard way. Any
help for a beginner would be appreciated.

 

[Sylvain Lafontaine]

Not sure but I think that you must add an equal sign before the IIF:

= iif([tank1product]="Unleaded",[tank1gallons],0)

You should also be careful if either values can be Null and take this into
account.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Jim L.]

Stupid mistake on my part. Working good now. Thanks for the help!!

Not sure but I think that you must add an equal sign before the IIF:

= iif([tank1product]="Unleaded",[tank1gallons],0)

You should also be careful if either values can be Null and take this into
account.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

I am working on a fuel inventory report. I have 11 tanks that may be
either
unleaded, premium or diesel, and may change throughout the year. The
product
in each tank, along with the amount, is entered on a form, and this report
is
based on the same table. I would like to have a total amount of each
product
shown on the report. I have made a text box with the following expression
in
the control source;

iif([tank1product]="Unleaded",[tank1gallons],0)

I believe this should display the amount of gallons in tank 1 in the text
box if the product is unleaded, and display a 0 if not. From here, I
would
do the same for each tank and each product, then add another text box
under
these to total each product. None of these boxes would be visible on the
report except the product totals.

Unfortunately, I am getting a syntax error about commas and/or parentheses
when I try the first box. I am probably going about this the hard way.
Any
help for a beginner would be appreciated.

 

[aaron.kempf]

sql server also has something smiliar-- but it's a lot lot lot more
powerful

select price, qty, case when tank1product = 'unleaded' then
tank1gallons
else 0 end as unleadedgallons
frm mySalesQuery

even better-- you could put this equation in a _TABLE_ so that then
you could re-use it (instead of having 200 different copies of the
same equation-- which makes it harder to maintain)

=Aaron

Not sure but I think that you must add an equal sign before the IIF:

    = iif([tank1product]="Unleaded",[tank1gallons],0)

You should also be careful if either values can be Null and take this into
account.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

I am working on a fuel inventory report.  I have 11 tanks that may be
either
unleaded, premium or diesel, and may change throughout the year.  The
product
in each tank, along with the amount, is entered on a form, and this report
is
based on the same table.  I would like to have a total amount of each
product
shown on the report.  I have made a text box with the following expression
in
the control source;

iif([tank1product]="Unleaded",[tank1gallons],0)

I believe this should display the amount of gallons in tank 1 in the text
box if the product is unleaded, and display a 0 if not.  From here, I
would
do the same for each tank and each product, then add another text box
under
these to total each product.  None of these boxes would be visible onthe
report except the product totals.

Unfortunately, I am getting a syntax error about commas and/or parentheses
when I try the first box.  I am probably going about this the hard way.
Any
help for a beginner would be appreciated.- Hide quoted text -

- Show quoted text -

 

[Tony Toews [MVP]]

even better-- you could put this equation in a _TABLE_ so that then
you could re-use it (instead of having 200 different copies of the
same equation-- which makes it harder to maintain)

Or, in Access, you can create one query and use that query in many
places.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/