Toby, here is the SQL view. I think I was to paste this info here. The results in TotalCom is #Error.
SELECT Tbl_CSCPricingAdvice.ProjectNo, Tbl_CSCPricingAdvice.MemberN0, Tbl_CSCPricingAdvice.Invoice_No, [NNU_Total]+[SP_Total]+[SS_Total]+[SHD_Total]+[CPPE_Total]+ [CMSSHD_Total]+[CMSNTP_Total]+[CMSCE_Total]+[ECMD_Total]+[CP_Total]+ [Matrix_Total]+[SL_Total]+[CPS_Total]+[RR_Total]+[ED_Total]+ [DA_Total]+[HCCF_Total]+[Beacon_Total]+[Beacon2_Total]+ [Beacon3_Total]+[CS_Total]+[CS2_Total]+[CS3_Total]+[ECMD1_Total]+ [ECMD2_Total]+[ECMD3_Total]+[ECMDE_Total]+[BNI_Total]+[BUP_Total]+ [Archive_Total]+[FFFO_Total]+[NCOA_Total]+[RSL_Total]+[RSDE_Total]+ [RSD_Total]+[RSAP_Total]+[Other_Total]+[Other2_Total] AS ProjTotal, Tbl_CSCPricingAdvice.NotEquifaxPro, [ProjTotal]+[NotEquifaxPro] AS AdjTotal, [AdjTotal]*1 AS GrandTotal, Tbl_CSCPricingAdvice.AM_SSNumber, Tbl_AccountManager.AM_Name, Tbl_CSCPricingAdvice.Status, Tbl_Company.Location, Tbl_Company.Vertical, Tbl_CSCPricingAdvice.AM1_Name, Tbl_CSCPricingAdvice.AM2_Name, Commissions.GrossBilling1, Commissions.ListAmount1, IIf([Grossbilling1]/[ListAmount1]>=1,1,(IIf ([Grossbilling1]>0,Format([Grossbilling1]/[ListAmount1],"#.##"),0))) AS CommissionNo1, IIf([Grossbilling1]>0,Format([CommissionNo1] *0.05,"#.###"),0) AS CommissionPer1, IIf([Grossbilling1]>0,IIf ([GrossBilling1]*[CommissionPer1]>5000,5000,Format([GrossBilling1]* [CommissionPer1],"$#.##")),0) AS ECMSCommission1, Commissions.GrossBilling2, Commissions.ListAmount2, IIf ([Grossbilling2]>0,Format([Grossbilling2]/[ListAmount2],"#.##"),0) AS CommissionNo2, IIf([Grossbilling2]>0,Format([CommissionNo2] *0.05,"#.###"),0) AS CommissionPer2, IIf([Grossbilling2]>0,IIf ([GrossBilling2]*[CommissionPer2]>5000,5000,Format([GrossBilling2]* [CommissionPer2],"$#.##")),0) AS ECMSCommission2, Tbl_AccountManager.AM_Location, Tbl_AccountManager.AM_Vertical, Commissions.Comm_MonthEnd, Commissions.DateSubmitted, Tbl_CSCPricingAdvice.AM2_location, Tbl_CSCPricingAdvice.AM2_Vertical, Tbl_Vertical.Vertical_Desc, Tbl_Location.Location_Desc, Commissions.DOS_ApprovalAM1, Commissions.DOS_ApprovalAM2, Tbl_CSCPricingAdvice.ProjectDesc, [ECMSCommission1]+[ECMSCommission2] AS TotalCom FROM (Tbl_Location INNER JOIN (Tbl_Vertical INNER JOIN Tbl_AccountManager ON Tbl_Vertical.Vertical_No = Tbl_AccountManager.AM_Vertical) ON Tbl_Location.Location_No = Tbl_AccountManager.AM_Location) INNER JOIN ((Tbl_CSCPricingAdvice INNER JOIN Tbl_Company ON Tbl_CSCPricingAdvice.MemberN0 = Tbl_Company.[Member#]) LEFT JOIN Commissions ON Tbl_CSCPricingAdvice.ProjectNo = Commissions.ProjectNo) ON Tbl_AccountManager.AM_SSNumber = Tbl_CSCPricingAdvice.AM_SSNumber WHERE (((Tbl_CSCPricingAdvice.Invoice_No) Is Null)); --- In [email protected], "Hoffman, Tobi K \(DYS\)" <[EMAIL PROTECTED]> wrote: > Sorry, what I think you need is: > > TotalCom: [Commission1]+[Commission2} > > (I was thinking in terms of forms rather than queries.) See if this > works. If it doesn't, you could get the SQL view up for the query and > let us see that. > > Tobi > > -----Original Message----- > From: [email protected] > [mailto:[EMAIL PROTECTED] On Behalf Of torres0157 > Sent: Thursday, September 15, 2005 11:17 AM > To: [email protected] > Subject: [AccessDevelopers] Re: Message in Query #Error > > > Tobi, > I tried what you suggested but still got #error in the result field > TotalCom in the query. What I did was right click the Field > name on the form (Commission1)and entered Txt in front of the field > name which changed to TxtCommission1 and displays in the Text Box. I > also Changed this field names in the query commission1 and > commission2. > TotalCom: [Txtcommission1]+[TxtCommission2} > Do I need to also changed the control source > > What am I doing wrong. > > Thanks > Armando Torres > > > > --- In [email protected], "Hoffman, Tobi K \(DYS\)" > <[EMAIL PROTECTED]> wrote: > > Have you given the textbox or query field a different name from > the fields it references? If you start by pulling a field from the > field list onto the form (or use the wizard in starting to make the > form), it will have the field's name as the textbox name. Then when > you make it a calculated field, it looks to Access like a circular > reference, so you have to rename the textbox or field. If you > don't, Access gives that unhelpful "#Error# when you go to Form or > Datasheet view. I generally add "txt" as a prefix on a form, or > some other variant of the field name, and that's enough. > > > > Tobi > > > > -----Original Message----- > > From: [email protected] > [mailto:[EMAIL PROTECTED] On Behalf Of torres0157 > > Sent: Thursday, September 15, 2005 9:48 AM > > To: [email protected] > > Subject: [AccessDevelopers] Re: Message in Query #Error > > > > > > Yes, > > I think the problem is the fields I am trying to total are > > calculated fields. Is this true??? > > > > Armando Torres > > > > > > --- In [email protected], "robsims99" > > <[EMAIL PROTECTED]> wrote: > > > Hi, > > > > > > Something a bit basic, but the field in the form or the field in > > the > > > table you're getting all the data from or entering into are all > > the > > > same data type? > > > > > > cheers, > > > > > > Rob S. > > > > > > > > > --- In [email protected], "torres0157" > > <[EMAIL PROTECTED]> > > > wrote: > > > > I keep getting an error message #error when adding a > commission > > > > total in a query to a grand total. > > > > > > > > any suggestions > > > > > > > > Armando Torres > > > > > > > > > > > > > > Please zip all files prior to uploading to Files section. > > Yahoo! Groups Links > > > > > > > Please zip all files prior to uploading to Files section. > Yahoo! Groups Links ------------------------ Yahoo! Groups Sponsor --------------------~--> Most low income households are not online. Help bridge the digital divide today! http://us.click.yahoo.com/cd_AJB/QnQLAA/TtwFAA/q7folB/TM --------------------------------------------------------------------~-> Please zip all files prior to uploading to Files section. Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/AccessDevelopers/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
