Re: Multiple calculations on same field in Query, by range of valu

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Stay with the original thread. If you haven't gone too far with your db,
you might consider turning on the Name Auto Correct features and getting rid
of those pesky spaces.

"Gregrenae" <Gregrenae@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1AAE2FCB-B7E3-404A-8D08-805B09F654F0@xxxxxxxxxxxxxxxx
> My apologies, "Post the original SQL". Do you mean to copy the info as I
> have done below or start a new thread or?
> Thanks Again
>
> Original Post:
> OK, I am new to Access, have studied hard and long, but apparently am
short
> on the long. Trying to write the following Query:
> Field One contains a numberic value(ie: 115 or 219 or 320)
> I create the following Calculated Field:
> Field Two:[Field One]
> Now I want to perform the following calculation:
> If >=1 AND <=100 put in $300.00
> If >=101 AND <=200 put in $400.00
> If >=201 AND <=300 put in $500.00
>
> What shoud the rest of the formula be?
> Field Two:[Field One]
>
> Formulas Tried (I am putting this into the second column of the Query
table,
> in the "Field")
> Amount we are Invoicing [SF of Blue Tarp Installed]: IIf (SF of Blue Tarp
> Installed >=1 and SF of Blue Tarp Installed <101, 300, IIF(SF of Blue Tarp
> Installed >=101 and SF of Blue Tarp Installed <201, 400, IIf(SF of Blue
Tarp
> Installed >= 201 and SF of Blue Tarp Installed <= 300, 500, 999)))
> [Amount we are Invoicing [SF of Blue Tarp Installed]]: IIf ([SF of
> Blue Tarp Installed] >=1 and [SF of Blue Tarp Installed] <101, 300,
> IIF([SF of Blue Tarp Installed] >=101 and [SF of Blue Tarp Installed]
> <201, 400, IIf([SF of Blue Tarp Installed] >= 201 and [SF of Blue Tarp
> Installed] <= 300, 500, 999)))
>
>
> "John Vinson" wrote:
>
> > On Fri, 1 Apr 2005 06:43:05 -0800, Gregrenae
> > <Gregrenae@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
> >
> > >John,
> > >Thanks for your time and consideration. However, I am still running
into a
> > >problem. When I put the formula in, I get the following error message,
> > >"[Amount we are invoicing [SF of Blue Tarp Installed]] is not a valid
name.
> > >Make sure it does not include invalid characters or punctuation that is
too
> > >long".
> > >When I use the exact same name and just do a simple calculation, ie:
*10, it
> > >works fine. All the same I changed the field name all the way down to
> > >"Tarp", still without success. I sure would like to know how to make
this
> > >work without having to creat another table. Do you have any
suggestions?
> > >Thanks
> >
> > Please post the actual SQL again.
> >
> > John W. Vinson[MVP]
> >


.

Relevant Pages

  • Re: Multiple calculations on same field in Query, by range of valu
    ... My apologies, "Post the original SQL". ... Trying to write the following Query: ... I create the following Calculated Field: ... Amount we are Invoicing [SF of Blue Tarp Installed]: ...
    (microsoft.public.access.queries)
  • Re: Else If Statements
    ... are saying I would have first take the select query with the calculated field ... in it and make it into a make table query and then do another select query. ... Within the origional table there is a post code field. ... postcode down to only show the first part of the postcode i.e. the alpha ...
    (microsoft.public.access.queries)
  • RE: Limit Text displayed in Combo box
    ... I would base the combo box selection off a query rather than a table. ... you can use a calculated field in a query to return all ... I have a form with a combobox that uses the table as its row source. ...
    (microsoft.public.access.modulesdaovba)
  • RE: Passing variables to Function
    ... Ok here is the calculated field from my query and my primary function. ... add previous cycle totals if they exist. ... be called from the qryReagent1 query. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Else If Statements
    ... I started with a table with about 8 columns in it and then pulled a query ... Within the origional table there is a post code field. ... postcode down to only show the first part of the postcode i.e. the alpha ... What I need to do then is to put another calculated field into the ...
    (microsoft.public.access.queries)