Re: if >= to, then...

I'm trying insert a lookup table, but I keep getting told that I need to
include parentheses around the sub-query. I have tried inserting them in
several different ways, but then end up with a syntax error?

I have taken out all the spaces out of my fields names, but that did not
change anything either. I am supposed to be writing all this in the field
section of a query, right? What else might I be doing wrong?

Sorry, but I am still very new to access!

"Duane Hookom" wrote:

John's solution of a lookup table is the most maintainable and ideal.
Wrapping your calculation in a single function is probably the next best. I
would not create a complex expression of Switch() or IIf()s since the ranges
are bound to change.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


"Tara" wrote:

Thanks John. I finally understand why MIN didn't work. The switch function
works well for me, but I don't quite understand how have a "translation table
Factors ". I will stick with the switch function.
Tara.

"John W. Vinson" wrote:

On Mon, 31 Dec 2007 20:29:00 -0800, Tara <Tara@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:

Hello,

I'm transferring a very complex file from excel into Access, but am having
trouble with one of my formulas. It works in excel, but when I type it in the
query, I either get a syntax error or it tells me I am trying to do too much.
I've tried breaking it down into smaller sections using the criteria
sections, but no luck. This is how my formula looks in excel:

if(X>=3000;(X-3000)*.08;0)
if(X>=2000;MIN(1000;(X-2000))*.12;0)
if(X>=1000;MIN(1000;(X-1000))*.14;0)
if(X<1000;(1000-X)*(-.10);0)

Thank you for any help!
Tara

Well... Access and Excel are quite different, even when they appear to be
similar. The Min() operator in Access returns the minimum value of a field
*across multiple rows*, not the lesser/least of its operands.

In this case you can use the Switch() function. It takes pairs of arguments,
evaluates them left to right, and returns the second member of the first pair
for which the first element is true. I'd use it twice:

Switch(X>=3000;x-3000;x>=2000;x-2000;x>=1000;x-1000;true;1000-x) *
Switch(X>=3000;.08;x>=2000;.12;x>=1000;.14;true;-.10)

A possibly better solution would be to have a translation table Factors with
three fields - XLow, XHigh, and Factor and values like

0; 1000; .10
1000; 2000; .14
2000;3000; .12
3000; 2000000000; .08

You could then use a Query like

SELECT x-XHigh * Factor
FROM yourtable
INNER JOIN Factors
ON yourtable.X >= Factors.XLow
AND yourtable.X < Factors.XHigh;


John W. Vinson [MVP]

.

Relevant Pages

  • Re: if >= to, then...
    ... I will stick with the switch function. ... I'm transferring a very complex file from excel into Access, ... query, I either get a syntax error or it tells me I am trying to do too much. ... and returns the second member of the first pair ...
    (microsoft.public.access.queries)
  • Re: if >= to, then...
    ... I'm transferring a very complex file from excel into Access, ... trouble with one of my formulas. ... query, I either get a syntax error or it tells me I am trying to do too much. ... and returns the second member of the first pair ...
    (microsoft.public.access.queries)
  • RE: Macro
    ... Then post the query results. ... My data is in an Excel file called ... Microsoft SQL Server 2000, which requires a server, VBA or Microsoft Query ... VBA code in excel suddenly became un-compileable. ...
    (microsoft.public.excel.programming)
  • Re: Excel Calcs in Access
    ... show below looks to me like an Excel sheet with one column of ... "John Nurick" wrote: ... below is the result of the same query but 1 line above. ... John Nurick [Microsoft Access MVP] ...
    (microsoft.public.access.modulesdaovba)
  • Re: Looking for Easiest Way to Create Report
    ... By recording Excel macros and editing them you may be able to get all this down to one button push in each application. ... run your query then that makes life very much easier than it would have been if you'd had to match values line by line. ... So, if you do go for the query and TransferSpreadsheet route, that leaves you with the problem of levering in the four header rows. ... My guess is that the simplest solution will be in Excel, if you're prepared to do the job in two stages: one which generates the spreadsheet from Access, and another which requires you to move to Excel to add the headers. ...
    (microsoft.public.access.gettingstarted)