Kathy,
You would be doing yourself a favor by using only alphanumeric characters
and the underscore (no spaces) in names of objects, fields, and controls,
and by using the shortest possible name that will enable you as developer to
manage things behind the scenes. It's up to you, but even if you choose to
use long descriptive names for your own purposes, you would be helping
yourself here if you describe what is going on, and edit the names to make
them easier to understand. For instance:
[Direct Labour Analysis Qry3 Code 6 Sum.Sum Of Total Hours]
[Direct Labour Analysis Qry2 Code 3 Sum.Sum Of Total Hours]
don't describe what you are trying to do to those of us who can't see the
database.
However, in case there is something going on that is not clear from your
description, and if everything works fine as long as there is OT, you could
try wrapping the Nz function around the part that is giving you trouble.
Help has more information about Nz, but in general I think that if there is
a null in the calculation the entire calculation will fail. The default
value of the OT field could be set to 0 (and changed to 0 in the fields
containing no value), or you could use Nz, something like this:
[FirstField] + Nz([SecondField],0)
This is just a guess. As I said, your exact situation is rather unclear, at
least to me.
Kathy said:
It's copied pasted directly form the report so what you see is exact.
These
refer to 2 separate queries. Each query extracts information for different
hourly rates. I've been using the report for months and it worked great.
This
is the first time there have been no OT hours so it makes me think that it
doesn't like the null fields. Running each query separately, they all run
except for one (the one that extracts OT at code 3 rate) this is the code
in
which employees have no OT. Maybe I'm looking in the wrong direction to
fix
my problem?
Allen Browne said:
Is there a dot in the field name?
Or is the field name just the [Sum of Total Hours] part?
Or do these refer to subreports? Is so, see:
Bring the total from a subreport onto a main report
at:
http://allenbrowne.com/casu-18.html
for an explanation of how to test the HasData property of the subreport.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
Kathy said:
I tried using:
IIf([Report].[HasData], Sum([Direct Labour Analysis Qry3 Code 6 Sum.Sum
Of
Total Hours]+[Direct Labour Analysis Qry2 Code 3 Sum.Sum Of Total
Hours]),
Null)
But it's still giving me the syntex error message.
:
The first argument contains the literal word "Report", i.e. don't
change
this part:
=IIf([Report].[HasData],
The actual text box calculation is:
=Sum([Direct Labour Analysis Qry3 Code 6 Sum.Sum Of Total
Hours]+[Direct
Labour Analysis Qry2 Code 3 Sum.Sum Of Total Hours])
and the report is called Direct Labour Analysis Report.
So are you saying that I should change it to:
IIf([Direct Labour Analysis Report].[HasData], Sum([Direct Labour
Analysis
Qry3 Code 6 Sum.Sum Of Total Hours]+[Direct Labour Analysis Qry2
Code 3
Sum.Sum Of Total Hours]), Null)
?
I think I did something wrong. It's telling me I have a syntex
error.
:
Test the HasData property of the report to see if it has any
records.
Presumably you have a text box with some kind of calcuation such
as:
=Sum([Amount])
Change it to:
=IIf([Report].[HasData], Sum([Amount]), Null)
I have a report that calculates weekly payroll. It is broken down
into 4
categories. Employees making under a specific wage, the OT for
those
employees, Employees making over that wage and those employees
OT.
My
problem
is if there is no OT for either group I get all #Error messages
in
all
fields. Is there a simple way to tell the report that if there
are
no
records
to count/calculate to put in a zero? I am a novice and do not
write
code
well.
