ACCESS FEED: Re: [MS_AccessPros] Calculation in report is incorrect.

Senin, 15 Juli 2013

Re: [MS_AccessPros] Calculation in report is incorrect.

The difference is $1955, are there any other numbers or sums of numbers that equal the $1955 on the report?

----- Original Message -----

From: "Jim Wagner" <luvmymelody@yahoo.com>
To: "MS Access Professionals" <MS_Access_Professionals@yahoogroups.com>
Sent: Monday, July 15, 2013 3:15:02 PM
Subject: Re: [MS_AccessPros] Calculation in report is incorrect.

The syntax works, and the report opens again, but I am back to the original problem

On the report I have the following fields that are added together but do not add up correctly

$0 $1,959 $1,113,017 $2,307,445 $21,250 $284,820

equals on the report as $3,726,536 but on a calculator and excel equal $3,728,491

What is so funny is that nobody noticed it for several years.

Jim Wagner
________________________________

________________________________
From: Bill Mosca < wrmosca@comcast.net >
To: MS_Access_Professionals@yahoogroups.com
Sent: Monday, July 15, 2013 11:53 AM
Subject: Re: [MS_AccessPros] Calculation in report is incorrect.

Oh, for Pete's sake! Thanks, Duane.

Jim - try CLng instead of CInt.

-Bill

--- In MS_Access_Professionals@yahoogroups.com , Duane Hookom <duanehookom@...> wrote:
>
> Jim,
>
> Do you understand the CInt() function and its scale? I typed CInt into Help and found
>
> CInt Integer -32,768 to 32,767; fractions are rounded.
>
> Are your values within that range? If not, you should be able to find a replacement function that will work.
>
> Duane Hookom MVP
> MS Access
>
>
> ----------------------------------------
> > From: luvmymelody@...
> >
> > Bill,
> >
> > Back to work.
> >
> > I put this in the query and I get an overflow error
> >
> > rpttype: Sum(CInt(Nz([Safety],0))+CInt(Nz([DamageWear],0)+CInt(Nz([CodesStandards],0)+CInt(Nz([EnvironmentImprovement],0)+CInt(Nz([EnergyConservation],0)+CInt(Nz([Asbestos],0)))))))
> >
> >
> > Jim Wagner
> > ________________________________
> >
> >
> >
> > ________________________________
> > From: Jim Wagner <luvmymelody@...>
> >
> > Bill,
> >
> > does this go in the crosstab in place of the rpttype field I have?
> >
> > Jim Wagner
> > ________________________________
> >
> > ________________________________
> > From: Bill Mosca <wrmosca@...>
> >
> > Jim
> >
> > Somehow I don't think nulls are playing into this, but try using the Nz() function to avoid nulls. I think you'll also have to convert the Nz result to a number.
> > Sum(CInt(Nz([Safety],0))+CInt(Nz([DamageWear],0))...)
> >
> > Bill
> >
> > --- In MS_Access_Professionals@yahoogroups.com , Jim Wagner <luvmymelody@> wrote:
> >>
> >> Duane,
> >>
> >> I am at a disadvantage because somebody else built it and nepotism is a factor here.Â
> >>
> >> I do not know why I called it departments. force of habit. But yes campus is the field that is the columns. And in the crosstab it is a column heading
> >>
> >> Yes there are fields that are null.Â
> >>
> >> Soapbox
> >>
> >> The reporttypedescription field is the type of value like plumbing, or floors or electrical. The column with rpttype that has the calculation is adding up all of these things together for each reporttypedescription
>

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]