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