Tobi, Why can't anyone figure out how to spell our names?? It can't be that hard. :)
Cheers, Toby ----- Original Message ----- From: "Hoffman, Tobi K (DYS)" <[EMAIL PROTECTED]> To: <[email protected]> Sent: Thursday, September 15, 2005 11:07 AM Subject: RE: [AccessDevelopers] Re: Message in Query #Error > Ah, if you have a null value, then what you need is the "NZ" function: > > NZ([Commission1],0)) > > will return 0 if there is no value in Commission1. > > I still say you need to normalize your data -- and my name is TOBI, not > Toby! > > TOBI > > -----Original Message----- > From: [email protected] > [mailto:[EMAIL PROTECTED] On Behalf Of torres0157 > Sent: Thursday, September 15, 2005 2:00 PM > To: [email protected] > Subject: [AccessDevelopers] Re: Message in Query #Error > > > Toby, > If the value in Commission1 is 39.00 and 0.00 in Commission2 the result > in TotalCom will display 39.00. "correct" So, TotalCom will display or > sum correctly if there is only one value greater then zero in either > Commission1 or Commission2. If there is a value in both Commission > Fields TotalCom will display #Error. > > Wierd.... What is wrong > Can Someone explain this........ > > Armando Torres > > > > > --- In [email protected], "torres0157" > <[EMAIL PROTECTED]> wrote: > > Toby, > > I Don't think this query is very complex, however I may be wrong. As I > > > have stated can calculated fields be totaled within the same query > > they are be created. can someone Help.. > > > > Armando Torres > > > > > > > > --- In [email protected], "Hoffman, Tobi K \(DYS\)" > > <[EMAIL PROTECTED]> wrote: > > > Yikes! My advice is to divide and conquer; the query may simply > > be too > > > complex. Create queries that pull together some of the totals, > > then > > > pull those into the original query replacing the tables that > > originally > > > had those fields. Either that, or you need to look at > normalizing > > your > > > data, creating a related table that holds some of the fields. > > > > > > Tobi (with an eye, not a why!) > > > > > > -----Original Message----- > > > From: [email protected] > > > [mailto:[EMAIL PROTECTED] On Behalf Of torres0157 > > > Sent: Thursday, September 15, 2005 12:33 PM > > > To: [email protected] > > > Subject: [AccessDevelopers] Re: Message in Query #Error > > > > > > > > > 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 > > > > > > ------------------------ Yahoo! Groups Sponsor --------------------~--> Fair play? Video games influencing politics. Click and talk back! http://us.click.yahoo.com/T8sf5C/tzNLAA/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/
