Thanks for your response I tried your calculate sample it still works
great
on my system but on the other computers on the network I still get the
#error
where the total should be My little knowledge of access is not
understanding
how it works on some computers and not on others that have the same
programs
thanks for any help or suggestion you can give me
Jerry
:
Okay, I think I follow what you are doing here.
It might be easier to calculate the conditions as one.
Needs Nz() so nulls don't mess up the calculation.
Could multiply by 24 and drop the sign at the end.
Try something like this:
=24 * Abs(Sum(IIf(
([mpcatname]="Youth" And [master program]="non-dcf") OR
([mpcatname]="Science Base Youth" And [master program]="non-dcf") OR
([mpcatname]="Youth" And [master program]="dcf") OR
([mpcatname]="Science Base Youth" And [master program]="dcf"),
Nz([stoptime] - [starttime],0) - Nz([TimeLunchIn] - [TimeLunchOut],0),
0)))
Thanks again for responding the control source that i am using is:
=Sum(((((Abs([mpcatname]="Youth" And [master
program]="non-dcf")*(([stoptime]-[starttime])-([TimeLunchIn]-[TimeLunchOut])))*24)+((Abs([mpcatname]="Science
Base Youth" And [master
program]="non-dcf")*(([stoptime]-[starttime])-([TimeLunchIn]-[TimeLunchOut])))*24)))+((((Abs([mpcatname]="Youth"
And [master
program]="dcf")*(([stoptime]-[starttime])-([TimeLunchIn]-[TimeLunchOut])))*24)+((Abs([mpcatname]="Science
Base Youth" And [master
program]="dcf")*(([stoptime]-[starttime])-([TimeLunchIn]-[TimeLunchOut])))*24))))
each abs function gives me a total time for each catorgory when i
add
all
four together it works fine on most computers on the server all the
time
but
on some where this total is to be displayed is where i receive the
#error
If
I change the formula to any (3) of the catorgaories it will display
the
grand
total on all computer but when i add the 4th it will not work but
only
on
some computers On the other computers it works great giving the
total
hrs
spent in all 4 catorgories
:
Jerry, could you be more specific about what is in the Control
Source
of
the
final text box (the one that gives #Error), as well as the ones it
depends
on.
I don't understand how you are using the Abs() function with
date/time
fields.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
Thanks for your response ans suggestions
I am not useing a field name "abs" but the function
I am adding date/time fields The report works every time on my
computer
at
work and on the computers I get the error in the grandtotal
field
it
displays #error every time For some reason it is not an
intermittent
problem
but very consistence I have checked for nulls and no data fields
I
checked
the JET version The version is the same one The sub totals that i
use
adding
(2) DIFFERENT abs calculation together work fine on all computers
When
i
add
both sub totals together for a grand total is where the #error
message
come
on but only on some computers On all the other computers the
report
totals
correct and is fine Thanks for your suggestion and help If there
is
something
else i can look at please advise I am very limited on access but
learning
as
I go
Jerry
:
Jerry, there are several possibilities here:
1. Names
=======
I'm not clear whether you have a field named "abs"? If so, this
is
also
the
name of a VBA function, which is likely to cause the kind of
intermittent
problem you describe.
Solution: Rename the field (or at least alias it in the query
that
feeds
the
report.) For a list of names that can cause problems, see:
Problem names and reserved words
at:
http://allenbrowne.com/AppIssueBadWord.html
2. Types
======
With calculated controls, Access can misinterpret the data type,
and
this
can also be intermittent.
Solution: Set the Format property of the controls on the report
to
General
Number or Currency or something that indicates they are numbers,
and
explicitly typecast any calculated fields in the source query.
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html
3. Nulls
======
If you have several totals, and you then sum them together into
another
text
box with a Control Source like this:
=[Text0] + [Text1] + [Text2]
then if any one of them is Null, the result is Null. This
problem
is
intermittent depending on the data for the report.
Solution: Use Nz() to specify zero for null:
=Nz([Text0],0) + Nz([Text1],0) + Nz([Text2],0)
Issue #2 in this article has more info:
Common Errors with Null
at:
http://allenbrowne.com/casu-12.html
4. No Data
========
If a report (or subreport) has no data, attempting to sum the
non-existent
data returns an errror. This problem is intermittent in that it
depends
on
the actual data or filter.
Solution: Test the HasData property of the report, e.g.:
=IIf([Report].HasData, Nz(Sum([MyAmountField]),0), 0)
If you are using a subreport, see:
Bring the total from a subreport onto a main report
at:
http://allenbrowne.com/casu-18.html
5. JET version
===========
If none of the above apply, it is possible that the problem
computer
has
a
different version of msjet40.dll. Locate this file (typically in
windows\system32) in My Computer, right-click and choose
Properties.
On
the
version tab, you should see:
4.0.8xxx.0
The xxx digits don't matter, and you may even see a 9 on a
Windows
2000
machine. But if the minor version number is lower than 8,
download
and
apply
SP8 for JET 4 from one of these links:
http://support.microsoft.com/gp/sp
http://support.microsoft.com/kb/239114
When i try to use the Sum(abs) in a report it works great on
my
system
on
the
network But on some when it prints some totals are correcr but
the
grand
total displays an #error in the field Other computers on same
network
works
ok Prints all totals with out an error message Grand total is
(4)
different
Sum(abs) to generate the grand total If I sum (3) the report
works
great
When
I add the 4th It always displays the error I tried upgrading
one
computer
with Access 2003 to see if that would work I still have the
same
problem
Can
you advise me where I can go from here
