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 <*> 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/
