Re: if >= to, then...
- From: Tara <Tara@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 2 Jan 2008 17:30:03 -0800
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.
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
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.
"John W. Vinson" wrote:
On Mon, 31 Dec 2007 20:29:00 -0800, Tara <Tara@xxxxxxxxxxxxxxxxxxxxxxxxx>
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:
Thank you for any help!
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:
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
3000; 2000000000; .08
You could then use a Query like
SELECT x-XHigh * Factor
INNER JOIN Factors
ON yourtable.X >= Factors.XLow
AND yourtable.X < Factors.XHigh;
John W. Vinson [MVP]
- Prev by Date: Re: query for financial year
- Next by Date: Re: first record query
- Previous by thread: Re: if >= to, then...
- Next by thread: Re: if >= to, then...