Re: Null values and calculations

You can use the IIf() expression in the Control Source of a text box.

Duane is right: another cause of #Error is calculated expressions when the report has no records. Test the report's HasData property in IIf().

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Karina M ;)" <KarinaM@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8DD4E0A3-D143-499B-895E-F08E684A9051@xxxxxxxxxxxxxxxx
Thanks, Allen for the extra info.
I've never used IIF statements in queries, just in reports. Where would I
place the IIF statement within the query? Any other fields I need to set to
make it work?

Duane:
My query has some calculated fields from tables and some coming directly
from a table. The ones coming directly may have null values which I need to
be represented by a TRUE zero, as I perform calculations based on these
values. nZ made all null. Allen's answer sounds useful as the values would
come into the report in the favored format. I'll try this first.
Thanks!

"Duane Hookom" wrote:

There is another cause of #error. If the report returns no records, then any
value calculated in the report header or footer section will display #error.

I think you need to tell us more significant about your report's record
source etc.

--
Duane Hookom
Microsoft Access MVP


"Allen Browne" wrote:

> Lots of things can cause #Error. And once Access discovers a calculated
> control it cannot resolve, it gives up on the others. Consequently you > may
> have only one bad one, yet the others show #Error as well.
>
> A common cause is a control that has the same name as a field, but is > bound
> to something else. For example, if this control is named PRS TAT, but > bound
> to the expression you posted, Access can't figure out what to do with > it.
> Change its Name property to (say) txtPrsTat.
>
> Sometimes Access will barf if you refer to a field that is not actually > on
> the report, e.g. if there is no text box for PRS TAT. It seems that the
> optimizer doesn't bother fetching all fields if there's no control for > the
> field (depending on what other sorting/grouping is going on.) Add a > text box
> for the field, and hide it by setting its Visible property to No.
>
> Other causes include an erroneous expression, division by zero, or bad > data
> types (e.g. trying to sum text fields.)
>
> That last one is particularly releveant when you use Nz() in a query. > JET
> will treat the results as Text instead of Number. You can see that in > any
> query, as the column output is left-aligned like text, instead of
> right-aligned as a number. Therefore I prefer to use IIf() rather than > Nz()
> in a query. For example, instead of:
> Nz([PRS TAT], 0)
> use:
> IIf([PRS TAT] Is Null, 0, [PRS TAT])
>
> In your case, the expression is in the Control Source of a text box, so > you
> might be able to circumvent the problem merely by setting the Format
> property of the text box to something numeric, e.g. General Number, or
> Currency.
>
> So how do you find which of your calculated text boxes is causing the > error?
> Divide the task in half repeatedly until you pin it down. Without > saving,
> delete half the calculated controls. Result:
> - Still there? delete half the remaining ones.
> - Gone? put half them back.
>
> HTH
>
> -- > Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Karina M ;)" <KarinaM@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:3523F51D-01BA-4148-AF98-4CC06266424F@xxxxxxxxxxxxxxxx
> > Hmmm...that made the report return with no values at all. There were
> > #Error
> > messages where this formaul was being used in a calculations. Are > > there
> > any
> > other factors that might cause this?
> > Thanks!
> >
> > "Karina M ;)" wrote:
> >
> >> I have a null value that appears as a zero in a report as the > >> control is
> >> "=nZ([PRS TAT],0)". Yet, I am unable to deduct this zero value from > >> a
> >> second
> >> value. The field just comes out blank. Is this null zero a true zero > >> or
> >> can
> >> is not be used for calculations. If not, any suggestions on what I > >> can
> >> use
> >> instead?
> >> Thanks!
>
>

.

Relevant Pages

  • Re: Null values and calculations
    ... My query has some calculated fields from tables and some coming directly ... come into the report in the favored format. ... A common cause is a control that has the same name as a field, ... messages where this formaul was being used in a calculations. ...
    (microsoft.public.access.reports)
  • Re: Null values and calculations
    ... If the report returns no records, ... A common cause is a control that has the same name as a field, ... That last one is particularly releveant when you use Nzin a query. ... messages where this formaul was being used in a calculations. ...
    (microsoft.public.access.reports)
  • Re: Using the IsError Function
    ... On a report, may refer to a control. ... A control may be missing if a section is missing: ... The VBA IIF is slightly different from the Jet SQL IIF. ...
    (microsoft.public.access.reports)
  • Re: Peculiar Problem with Controls
    ... Thanks Allen, it was the name conflict causing the problem, now if you can ... rename the control. ... >>I have a report which utilises various calculations mostly done in the ... However in the report I need to divide all ...
    (microsoft.public.access.reports)
  • Re: Display control dependant on contents of another control
    ... Darhl ... > I've gotta learn that IIF better. ... >> Simplest thing is to add a text box to the report, and set its Control ...
    (microsoft.public.access.forms)
Loading