iif() in Reports

  • Thread starter Thread starter Scott Viney
  • Start date Start date
S

Scott Viney

G´day All,

I have a report that writes out the data for a quote of items i.e. quantity,
product, packaging, price, total for item, etc. All my data for the quote
comes from a query related to the tables

In the detail section if the quantity for an item is 0 I want the price &
tax to say $0 not to show the price we have if we´re not going to quote.
These two columns of data are from a table with a currency data type.

But when I use iff ([Quantity]=0,0,[Price]) for say the price, when I open
the items with a quantity of 0 work properly but all the other records now
have ERROR.

Has it got something to do with the data type? Because I have done
something similar on the two calculated columns from the query to print "NOT
QUOTED" and it works fine.

Hellllppp, Iam going bonkers, cheers,
Scott
 
Make sure that the textbox name does not have the
name "Price" or "Quantity" or the name of any other field
in your source data.
When you create a formula is is best to start with an
UNBOUND textbox.
Hope this helps.
Fons
 
Hi Scott,

If what Fons wrote doesn't work you could try and get a
bit more tricky and write a private Function within your
report that will do the logic work for you. In an unbound
textbox on your report that you want it to show a price
of Zero if the quantity is Zero you could use a function
called Pricelookkup and enter it as "=PriceLookup
([Quantity], [UnitPrice])" in the control Source of the
unbound testbox (where [Quantity] refers to the quantity
field in your report not the textbox that the value is
represented in). Then in your reports code you could
write something like

Private Function PriceLookup(ItemQuantity as double,
PricePerUnit as currency) as currency

If ItemQuantity = 0 then
PriceLookup = 0
else
PriceLookup = Format
(ItemQuantity*PricePerUnit, "Currency")
end if

Now what will happen is that you won't be depending on a
calculated field in your underlying query, this function
will do all the logic calculation for you.

Hope this little trick helps. I use this kind of stuff a
lot in my reports because you can't always get all the
logic into a query. Sometimes I freak queries out by
making my criteria waaayyy to complex and it tells me
to 'simplify' the criteria. If life were only so simple!!!
 
Thanks for the tip Fons,

I have tried your suggestion by placing an unbound text box in the detail
section and tried the following.
in the control source iif([Quantity]=0,0,[Price]) but I get ERROR in this in
my report as well now.

There must be something else Iam doing wrong. What Iam trying to do with
iff() seems like pretty normal stuff.

Have a good one,
Scott

Make sure that the textbox name does not have the
name "Price" or "Quantity" or the name of any other field
in your source data.
When you create a formula is is best to start with an
UNBOUND textbox.
Hope this helps.
Fons
 
Thanks,

I dont seem to be able to get this to work either. I must have something
wrong with what Im doing.


<[email protected]> escribió en el mensaje
Hi Scott,

If what Fons wrote doesn't work you could try and get a
bit more tricky and write a private Function within your
report that will do the logic work for you. In an unbound
textbox on your report that you want it to show a price
of Zero if the quantity is Zero you could use a function
called Pricelookkup and enter it as "=PriceLookup
([Quantity], [UnitPrice])" in the control Source of the
unbound testbox (where [Quantity] refers to the quantity
field in your report not the textbox that the value is
represented in). Then in your reports code you could
write something like

Private Function PriceLookup(ItemQuantity as double,
PricePerUnit as currency) as currency

If ItemQuantity = 0 then
PriceLookup = 0
else
PriceLookup = Format
(ItemQuantity*PricePerUnit, "Currency")
end if

Now what will happen is that you won't be depending on a
calculated field in your underlying query, this function
will do all the logic calculation for you.

Hope this little trick helps. I use this kind of stuff a
lot in my reports because you can't always get all the
logic into a query. Sometimes I freak queries out by
making my criteria waaayyy to complex and it tells me
to 'simplify' the criteria. If life were only so simple!!!
 
Scott, sorry I was not on my computer this weekend.
I'll gladly look at what the problem may be if you can
send me a small sample of your database.
If you choose to do so my email is fonsponsio at the
provider sbcglobal.net
Sorry it did not work so far.
Fons
-----Original Message-----
Thanks for the tip Fons,

I have tried your suggestion by placing an unbound text box in the detail
section and tried the following.
in the control source iif([Quantity]=0,0,[Price]) but I get ERROR in this in
my report as well now.

There must be something else Iam doing wrong. What Iam trying to do with
iff() seems like pretty normal stuff.

Have a good one,
Scott

Make sure that the textbox name does not have the
name "Price" or "Quantity" or the name of any other field
in your source data.
When you create a formula is is best to start with an
UNBOUND textbox.
Hope this helps.
Fons
-----Original Message-----
G´day All,

I have a report that writes out the data for a quote of items i.e. quantity,
product, packaging, price, total for item, etc. All my data for the quote
comes from a query related to the tables

In the detail section if the quantity for an item is 0 I want the price &
tax to say $0 not to show the price we have if we´re not going to quote.
These two columns of data are from a table with a currency data type.

But when I use iff ([Quantity]=0,0,[Price]) for say the price, when I open
the items with a quantity of 0 work properly but all the other records now
have ERROR.

Has it got something to do with the data type? Because I have done
something similar on the two calculated columns from the query to print "NOT
QUOTED" and it works fine.

Hellllppp, Iam going bonkers, cheers,
Scott



.
Click to expand...


.
Click to expand...
 
Back
Top