Re: [MS_AccessPros] Calculation in report is incorrect.

 

Jim

Maybe it's just too complex to be resolved. Try it without the CInt.

Bill

--- In MS_Access_Professionals@yahoogroups.com, Jim Wagner <luvmymelody@...> wrote:
>
> Bill,
>
> I still get an overflow error with the blocks. 
>  
> Jim Wagner
> ________________________________
>
>
>
> ________________________________
> From: Bill Mosca <wrmosca@...>
> To: MS_Access_Professionals@yahoogroups.com
> Sent: Monday, July 15, 2013 11:09 AM
> Subject: Re: [MS_AccessPros] Calculation in report is incorrect.
>
>
>
>  
> Jim
>
> Your parentheses are not correct. Each field should look like this:
> CInt(Nz([MyField]))
>
> Let's call that a block. So the line would be:
> Sum(block+block+block)
>
> Yahoo will probably screw this up but here is how it should be:
>
> 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)))
>
> Bill
>
> --- In MS_Access_Professionals@yahoogroups.com, Jim Wagner <luvmymelody@> wrote:
> >
> > 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@>
> > To: "MS_Access_Professionals@yahoogroups.com" <MS_Access_Professionals@yahoogroups.com>
> > Sent: Friday, July 12, 2013 3:00 PM
> > Subject: Re: [MS_AccessPros] Calculation in report is incorrect.
> >
> >
> >
> >  
> > Bill,
> >
> > does this go in the crosstab in place of the rpttype field I have?
> >  
> > Jim Wagner
> > ________________________________
> >
> > ________________________________
> > From: Bill Mosca <wrmosca@>
> > To: MS_Access_Professionals@yahoogroups.com
> > Sent: Friday, July 12, 2013 1:14 PM
> > Subject: Re: [MS_AccessPros] Calculation in report is incorrect.
> >
> >
> >  
> > 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
> > >
> > >  
> > >
> > >
> > >
> > >
> > >
> > > Jim Wagner
> > > ________________________________
> > >
> > >
> > >
> > > ________________________________
> > > From: Duane Hookom <duanehookom@>
> > > To: Access Professionals Yahoo Group <ms_access_professionals@yahoogroups.com>
> > > Sent: Friday, July 12, 2013 12:33 PM
> > > Subject: RE: [MS_AccessPros] Calculation in report is incorrect.
> > >
> > >
> > >
> > >  
> > > Jim,
> > >
> > > "I need the crosstab to get the different departments as columns" but there is no field named department. Is this supposed to be the Campus field?
> > >
> > > Since you are building a report, I would expect you to include all values of the Campus field in the Column Headings property.
> > >
> > > Are any of your fields possibly null? Do you understand that "[Safety]+[DamageWear]+[CodesStandards]+[EnvironmentImprovement]+[EnergyConservation]+[Asbestos]" will return Null if any of the values are Null?
> > >
> > > <soapbox>
> > > Do you understand you are committing spreadsheet by having fields like [Safety]+[DamageWear]+[CodesStandards]+[EnvironmentImprovement]+[EnergyConservation]+[Asbestos]? Or, are these columns the result of a crosstab? What happens when your customer needs to add another attribute to track? Are you going to have to add another field and then modify forms, reports, queries, ...?
> > > </soapbox>
> > >
> > > Duane Hookom MVP
> > > MS Access
> > >
> > > ----------------------------------------
> > > > From: luvmymelody@
> > > >
> > > > Bill,
> > > >
> > > > I created a select query and the values in the query add up correctly. I put the sql below. But I need the crosstab to get the different departments as columns.
> > > >
> > > > Jim
> > > >
> > > > SELECT [Building Inspection Data].Safety, [Building Inspection Data].DamageWear, [Building Inspection Data].CodesStandards, [Building Inspection Data].EnvironmentImprovement, [Building Inspection Data].EnergyConservation, [Building Inspection Data].Asbestos, Sum([Safety]+[DamageWear]+[CodesStandards]+[EnvironmentImprovement]+[EnergyConservation]+[Asbestos]) AS Jim
> > > > FROM [Building Inspection Data] INNER JOIN [VALUE LIST - Report Types] ON [Building Inspection Data].ReportType = [VALUE LIST - Report Types].ReportType
> > > > GROUP BY [Building Inspection Data].Safety, [Building Inspection Data].DamageWear, [Building Inspection Data].CodesStandards, [Building Inspection Data].EnvironmentImprovement, [Building Inspection Data].EnergyConservation, [Building Inspection Data].Asbestos;
> > > >
> > > > Jim Wagner
> > > > ________________________________
> > > >
> > > > ________________________________
> > > > From: Bill Mosca <wrmosca@>
> > > >
> > > > Jim
> > > >
> > > > What happens if you use a select query to add the columns?
> > > >
> > > > Bill Mosca
> > > >
> > > > --- In MS_Access_Professionals@yahoogroups.com, Jim Wagner <luvmymelody@> wrote:
> > > >>
> > > >> 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
> > > >>
> > > >>
> > > >>
> > > >> Jim Wagner
> > > >> ________________________________
> > > >>
> > > >>
> > > >>
> > > >> ________________________________
> > > >> From: luvmymelody <luvmymelody@>
> > > >> To: MS_Access_Professionals@yahoogroups.com
> > > >> Sent: Friday, July 12, 2013 8:59 AM
> > > >> Subject: [MS_AccessPros] Calculation in report is incorrect.
> > > >>
> > > >>
> > > >>
> > > >> ÃÆ'‚
> > > >> Hello all,
> > > >>
> > > >> I have a report based on a crosstab query and the data is correct that I see. But on the report several things have confused me.
> > > >>
> > > >> The fields in the report footer that calculate the columns are correct based on the query and putting the query in excel. They match exactly.
> > > >>
> > > >> The calculations for the rows are the ones are not calculating correctly. Subsequently the totals for the calculated column is off by 29K. I have formatted all controls to be Currency and 0 decimal points. How do I get the row calculation to be calculated correctly?
> > > >>
> > > >> Thank You
> > > >>
> > > >> Jim Wagner
> > > >>
> > > >> The sql is below
> > > >> TRANSFORM Sum([Safety]+[DamageWear]+[CodesStandards]+[EnvironmentImprovement]+[EnergyConservation]+[Asbestos]) AS rpttype
> > > >> SELECT [Building Inspection Data].ReportType, [VALUE LIST - Report Types].ReportTypeDescription, [VALUE LIST - Report Types].Order, [Building Inspection Data].Residential
> > > >> FROM [Building Inspection Data] INNER JOIN [VALUE LIST - Report Types] ON [Building Inspection Data].ReportType = [VALUE LIST - Report Types].ReportType
> > > >> GROUP BY [Building Inspection Data].ReportType, [VALUE LIST - Report Types].ReportTypeDescription, [VALUE LIST - Report Types].Order, [Building Inspection Data].Residential
> > > >> PIVOT [Building Inspection Data].Campus;
> > > >>
> > > >>
> > > >>
> > > >>
> > > >>
> > > >> [Non-text portions of this message have been removed]
> > > >>
> > > >
> > > >
> > > >
> > > >
> > > > [Non-text portions of this message have been removed]
> > > >
> > > >
> > > >
> > > > ------------------------------------
> > > >
> > > > Yahoo! Groups Links
> > > >
> > > >
> > > >
> > >
> > >
> > >
> > > [Non-text portions of this message have been removed]
> > >
> >
> > [Non-text portions of this message have been removed]
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
>
>
>
> [Non-text portions of this message have been removed]
>

__._,_.___

Reply via web post

Reply to sender

Reply to group

Start a New Topic

Messages in this topic (13)

Recent Activity:

• New Members 4

Visit Your Group

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use • Send us Feedback

.

__,_._,___