Re: IIF question

I have an additional hurdle of handling the "NA" because the field is a
numeric field and I get errors from trying to put a string in a numeric field.

Any thoughts?

"fredg" wrote:

> On Thu, 28 Jul 2005 13:46:04 -0700, LP wrote:
>
> > It's been awhile since I've done any Access queries and I need to calculate a
> > variance on actual sales vs forcast sales for a week. Below is what I've
> > tried.
> >
> > SalesPerVar: IIf(([MForcastSales] And
> > [MActSales])=0,0,IIf(([MForcastSales]=0 And
> > [MActSales])>0,"NA",([MForcastSales]-[MActSales])/[MForcastSales]*100))
>
> What are you trying to do here?
> IIf(([MForcastSales] And [MActSales])=0
>
> 1) Is this supposed to literally add the two fields? Then:
> IIf([MForcastSales]+[MActSales]=0, 0, .....
>
> 2) Are you checking that each field = 0? Then:
> IIf([MForcastSales] = 0 And [MActSales]=0, 0, ......
>
> Probably #2.
>
> Note: you also have the parentheses (which are not needed) in the
> wrong position in the criteria portion of both IIf's. The closing one,
> if you use parentheses, belongs after the =0, not before it.
>
> IIf(([MForcastSales] And [MActSales])=0
> should be
> IIf(([MForcastSales] And [MActSales]=0)
>
> IIf(([MForcastSales]=0 And [MActSales])> 0
> should be
> IIf([MForcastSales]=0 And [MActSales]> 0)
>
> Try this:
> IIf([MForcastSales] = 0 And [MActSales]= 0, 0, IIf([MForcastSales]=0
> And [MActSales] >0, "NA" , ([MForcastSales]-[MActSales])/
> [MForcastSales] *100))
>
> --
> Fred
> Please only reply to this newsgroup.
> I do not reply to personal email.
>
.

Relevant Pages

  • Re: variable on report
    ... A Date is simply a numeric field (it's an 8 byte floating point number, ... (no private e-mails, please) ... What I ended up doing was declaring the two DATE variables as STRING ... Public Function fWorkshopName() as String ...
    (microsoft.public.access.modulesdaovba)
  • Re: Check Box
    ... Did you see my statement "Assuming your year is stored as a numeric field, ... Doug Steele, Microsoft Access MVP ... My preference is to generate a string that uses the IN operator. ...
    (microsoft.public.access.forms)
  • Re: Number input translation to SQL
    ... numeric field. ... You have to parse the string on your own. ... collection of ValueArea objects. ... add a parse function to analyse the string. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Question on Numeric Formating...
    ... Are you sure that the dr.itemis retrieving a numeric field? ... Manuel Canas wrote: ... > I'm trying to convert this string into a curreny format type and I'm not ... > Error - Public Overridable Function ToString() As String' has no parameters ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Check Box
    ... Assuming your year is stored as a numeric field, you can't use Like with it: ... My preference is to generate a string that uses the IN operator. ... filter one at a time. ...
    (microsoft.public.access.forms)