A.T. In that case try: Me.Commission = IIf(IsNull(GrossBilling) Or GrossBilling = 0, Null, IIf(GrossBilling / ListAmount > 1, 1, Format(GrossBilling / ListAmount, "0.00")))
Glenn P. Jones IHS -----Original Message----- From: AccessDevelopers@yahoogroups.com [mailto:[EMAIL PROTECTED] On Behalf Of torres0157 Sent: Wednesday, 27 July, 2005 10:33 AM To: AccessDevelopers@yahoogroups.com Subject: [AccessDevelopers] Re: Help with Conditional Expression Glen, I am only concerned with the first digit before the deceimal and last 2 digits being zero, Howver you are right 2.52/3000.00 is = to 0.00075 in this case the commission should be 0.00. There could be a calculation of 2000.00/3000.00 = 0.67 which should be displayed. A.T. --- In AccessDevelopers@yahoogroups.com, "Jones, Glenn P MSG (Ret) FL-ARNG" <[EMAIL PROTECTED]> wrote: > Hi A.T. > When you say "The user does nothing if the billing amount is = Zero", are > you saying that he will not click the calc button? If so you do not need to > worry about it being zero > > When you said "However If the result of the calculation of > billing/listamount is = zero such as 2.52/3000.00 the commission field > displays #error." Note that 2.25/3000.00 does not equal = it equals > 0,00075. Therefore, you only need to calculate if the results of > GrossBilling/ListAmount if equal to or greater than one. > > In the OnClick event of your calculation command button try: > > Me.Commission = IIf(GrossBilling / ListAmount => 1, 1, "0.00")) > > Glenn P. Jones > > > > > -----Original Message----- > From: AccessDevelopers@yahoogroups.com > [mailto:[EMAIL PROTECTED] On Behalf Of torres0157 > Sent: Tuesday, 26 July, 2005 3:40 PM > To: AccessDevelopers@yahoogroups.com > Subject: [AccessDevelopers] Re: Help with Conditional Expression > > > Glen, > The user will enter on the form the Billing and the List amount and click > a compute button which will compute the > Commission. The user does nothing if the billing amount is = > zero. I have this condition to check that > if the commission > 1 place a one in the commission. However If the result > of the calculation of billing/listamount is = zero such as 2.52/3000.00 the > commission field displays #error. I need the commision field to display > 0.00 if this condition ocurrs. > > A.T. > > > > enter a compute which will > and the condition I have is that > --- In AccessDevelopers@yahoogroups.com, "Jones, Glenn P MSG (Ret) > FL-ARNG" <[EMAIL PROTECTED]> wrote: > > HI A.T. > > Are up trying to calculate if they are to receive a commission or > are you > > actually trying to calculate the commission? > > Can GrossBilling or ListAmount = 0? > > If you are trying to calculate commission, write down each > condition where > > they will or will not receive a commission. > > > > > > Glenn P. Jones > > > > > > > > > > -----Original Message----- > > From: AccessDevelopers@yahoogroups.com > > [mailto:[EMAIL PROTECTED] On Behalf Of torres0157 > > Sent: Tuesday, 26 July, 2005 1:57 PM > > To: AccessDevelopers@yahoogroups.com > > Subject: [AccessDevelopers] Re: Help with Conditional Expression > > > > > > Toby, > > I'm sorry the Commission field is not in a Table. It > > is generated in the query. > > > > Commission: IIf([GrossBilling]/[ListAmount]>=1,1, > > IIf([GrossBilling]>0,Format([GrossBilling]/[ListAmount], > > "#.##"),0))) > > > > A.T. > > > > --- In AccessDevelopers@yahoogroups.com, "Hoffman, Tobi K \(DYS\)" > > <[EMAIL PROTECTED]> wrote: > > > In the Field section for the query, enter the name as something > > like: > > > > > > CommField: {existing expression} > > > > > > This will show up in the SQL view as > > > > > > {expression} AS CommField > > > > > > (excuse my not repeating the whole "IIF" stuff in "expression"!) > > > > > > Tobi > > > > > > -----Original Message----- > > > From: AccessDevelopers@yahoogroups.com > > [mailto:[EMAIL PROTECTED] On Behalf Of torres0157 > > > Sent: Tuesday, July 26, 2005 1:43 PM > > > To: AccessDevelopers@yahoogroups.com > > > Subject: [AccessDevelopers] Re: Help with Conditional Expression > > > > > > > > > Tobi, > > > The field Commission is a field name in a table and this > > expression > > > is in a query. I tried your suggestion but received #errror in > the > > > text box on the form. I must be doing something wrong. > > > > > > A.T. > > > > > > > > > > > > > > > --- In AccessDevelopers@yahoogroups.com, "Hoffman, Tobi K > \(DYS\)" > > > <[EMAIL PROTECTED]> wrote: > > > > Rename the field to something like CommissionField -- that's > the > > > circular reference. > > > > > > > > Tobi (with an eye, not a why!) > > > > > > > > -----Original Message----- > > > > From: AccessDevelopers@yahoogroups.com > > > [mailto:[EMAIL PROTECTED] On Behalf Of torres0157 > > > > Sent: Tuesday, July 26, 2005 1:04 PM > > > > To: AccessDevelopers@yahoogroups.com > > > > Subject: [AccessDevelopers] Re: Help with Conditional > Expression > > > > > > > > > > > > Toby, > > > > I inserted your code but recieved an error msg: "Circular > > > > reference cauase by alias 'Commission' in query > > > definition > > > > select list" > > > > could this be because the Commission is the result of the > > > expression "[GrossBilling]/[ListAmount]. I am not an expert but > > > maybe the expression is check for a field before it is > calculated. > > > > > > > > A.T. > > > > > > > > > > > > > > > > > > > > > > > > > > > > --- In AccessDevelopers@yahoogroups.com, "Hoffman, Tobi K > > \(DYS\)" > > > > <[EMAIL PROTECTED]> wrote: > > > > > Put the check for commission first, nesting the rest inside: > > > > > > > > > > Commission: IIf([Commission]=0,0,IIf([GrossBilling]/ > > [ListAmount] > > > > >=1,1, > > > > > IIf([GrossBilling]>0,Format([GrossBilling]/ > > > > > [ListAmount],"#.##"),0)))) > > > > > > > > > > You might want to take the format part of this outside of the > > > > rest, rather than put it in twice (which I didn't). > > > > > > > > > > One further caution: if you're absolutely certain that no > other > > > > fat-fingered person will ever enter 0 for ListAmount, this > > > statement > > > > will work, but you might want to add another test for > > > ListAmount=0, > > > > since that will result in a division by zero error. > > > > > > > > > > Tobi > > > > > -----Original Message----- > > > > > From: AccessDevelopers@yahoogroups.com > > > > [mailto:[EMAIL PROTECTED] On Behalf Of > torres0157 > > > > > Sent: Tuesday, July 26, 2005 12:36 PM > > > > > To: AccessDevelopers@yahoogroups.com > > > > > Subject: [AccessDevelopers] Re: Help with Conditional > > Expression > > > > > > > > > > > > > > > Tom, > > > > > Thanks that was a fat finger error, but the query is keyed > > > > correctly However the problem still exists if the Commssion = > > zero. > > > > > > > > > > A.T. > > > > > > > > > > > > > > > --- In AccessDevelopers@yahoogroups.com, "Tom Oakes" > <[EMAIL PROTECTED]> > > > > > wrote: > > > > > > You're missing a closing quote on your Format (see red). > > > > > > > > > > > > Tom Oakes > > > > > > Personal PC Consultants, Inc. > > > > > > [EMAIL PROTECTED] > > > > > > 503.230.0911 (O) > > > > > > 402.968.6946 (C) > > > > > > 713.583.7091 (F) > > > > > > > > > > > > > > > > > > > > > > > > _____ > > > > > > > > > > > > From: AccessDevelopers@yahoogroups.com > > > > > > [mailto:[EMAIL PROTECTED] On Behalf Of > > > torres0157 > > > > > > Sent: Tuesday, July 26, 2005 9:22 AM > > > > > > To: AccessDevelopers@yahoogroups.com > > > > > > Subject: [AccessDevelopers] Help with Conditional > Expression > > > > > > > > > > > > > > > > > > Good Morning, > > > > > > I need some help in changing my condition statement > > > > > > > > > > > > Commission: IIf([GrossBilling]/[ListAmount]>=1,1, > > > > > > IIf([GrossBilling]>0,Format([GrossBilling]/ > > > > > [ListAmount],"#.##"),0))) > > > > > > > > > > > > I need to insert some extra conditions to check if > > Commision > > > > > > = zero to insert 0.00. [GrossBilling]/[ListAmount]=0,0. > > > > > > I tried but to no avail. I am still trying. > > > > > > Can someone Help... > > > > > > > > > > > > Thanks A.T. > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Please zip all files prior to uploading to Files section. > > > > > > > > > > > > > > > > > > > > > > > > _____ > > > > > > > > > > > > YAHOO! GROUPS LINKS > > > > > > > > > > > > > > > > > > > > > > > > * Visit your group "AccessDevelopers > > > > > > <http://groups.yahoo.com/group/AccessDevelopers> " on the > > web. > > > > > > > > > > > > > > > > > > * To unsubscribe from this group, send an email to: > > > > > > [EMAIL PROTECTED] > > > > > > <mailto:[EMAIL PROTECTED] > > > > > subject=Unsubscribe> > > > > > > > > > > > > > > > > > > * Your use of Yahoo! Groups is subject to the Yahoo! > > Terms of > > > > > Service > > > > > > <http://docs.yahoo.com/info/terms/> . > > > > > > > > > > > > > > > > > > _____ > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > 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 > > > > > > > > > > > > > > > > > > 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 > > > > > > 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 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/
