Re: if >= to, then...

Tech-Archive recommends: Fix windows errors by optimizing your registry

Did you try to use John's SQL syntax? Can you tell us anything about your
tables and fields and your attempted SQL view?

--
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:

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: Report Equation Access 2003
    ... Microsoft Access MVP ... "Duane Hookom" wrote: ... I have this syntax in this control source ... I want no figures to appear in the report when it is not equal to ...
    (microsoft.public.access.reports)
  • Re: how do i display the day of the week using the TODAY() function?
    ... If you're doing this in Microsoft Access, ... If you're doing it in Excel, the syntax will be somewhat different; ... you may want to ask the question in an Excel newsgroup so someone ...
    (microsoft.public.access.queries)
  • Re: Hyperlink field code
    ... maybe I still haven't understood the syntax correctly. ... Folder, Excel Subfolder). ... Entering hyperlink fields can be tricky. ... Files), click the Bookmark button, and select the range from the ...
    (microsoft.public.word.docmanagement)
  • Re: Need to make a vbs, but dont know how....
    ... Im not really sure that I can verify the syntax and im getting a syntax ... I dont think I have a vbscript editor, I usually use notepad, but I ... What setting do you want to change in Excel? ...
    (microsoft.public.excel.programming)
  • RE: Concatenate Function for unlimited characters
    ... I don't know how to get all the data into Excel and would need to either test ... export this data into Excel with the same results in the query. ... "Duane Hookom" wrote: ... Dim strConcat As String 'build return string ...
    (microsoft.public.access.queries)