Choose a cell based on data in other cells within the same record

[Eddy Sincere]

I want to have a query do the following:

If value A is greater than Col1 but less than Col2 then return cell Col3.

Here's an example:

Col1 | Col2 | Col3
1 | 5 | 100
6 | 10 | 200
11 | 20 | 300

I enter a value (8) into a record. The query runs and compares that value to the records in the table above. It would return 200 because 8 is greater than 6 but less than 10.

I'm thinking it would be something like an "If, Then" statement in Excel but am not sure how the statement should look in the query.

[ranman256]

select *, iif([a] between [col1] and [col2],[col3],null) as result from table
or
select *, iif([a] >= [col1] and [a] <=[col2],[col3],null) as result from table

[CJ_London]

no 'if' required - can do it with the critieria

SELECT Col3 FROM myTable WHERE [Enter an number] between Col1 and Col2

[Eddy Sincere]

Ajax -

I tried the method you described as it seemed easier. This is what I entered in the Field line:

Expr4: SELECT [DVRslt] FROM [DVVtmnMnrl] WHERE [Expr1] between [Lwr] and [Uppr]

However, it keeps telling me the syntax is incorrect and that I should enclose the subquery in parentheses.

[IrogSinta]

You don't enter it in a Field line. You need to change your query view from Design View to SQL view and add the Select string there. You then can switch back to Design View to see what it looks like in that view.

[June7]

Ajax's example is a full SQL statement, not an expression for a field.

[Eddy Sincere]

Um... yeah. I totally knew that.

So, after correctly adding the statement in SOL I get the error: "Syntax error in FROM clause."

Here's my SQL. I know it's something super simple but given that I'm just a hack it's beyond me.

SELECT [1Clrs].AmntClrs
FROM 1Clrs


SELECT Col4
FROM [DVVtmnMnrl]
WHERE [1Clrs].AmntClrs between Col2 and Col3;


Should I name the table and columns? Something like:

SELECT [DVVtmnMnrl].DVRslt
FROM DVVtmnMnrl
WHERE [1Clrs].AmntClrs between [DVVtmnMnrl]![Lwr] and [DVVtmnMnrl]![Uppr];

[CJ_London]

Not at all clear what you are trying to achieve and your sql does not make sense - perhaps better to explain what tables you have, their field names and any references to forms (form name and control name)

[Eddy Sincere]

Sure.

I have 2 tables:

Table 1 - Clrs - It has an amount filed (ClrsAmnt).
Tbale 2 - DVVtmnMnrl - It has three columns, Upper, Lower and DVRslt

I want the query to to pull the ClrsAmnt value, compare it to the Upper and Lower values in DVVtmnMnrl table and return the respective DVRslt value. Your solution above seems to be what I need, I just don't know the specific syntax. I attached the DB in it's basic form so you can see what I'm talking about.

Also, there's an example in my original post.

[CJ_London]

not at all clear what you require but suspect you mean this

SELECT DVVtmnMnrl.DVRslt
FROM 1Clrs, DVVtmnMnrl
WHERE ((([1Clrs].AmntClrs) Between [lwr] And [uppr]));

[Eddy Sincere]

For not understanding what I'm asking for you sure nailed it. That's precisely what I'm looking for. Thanks a lot!

Basically, I want a primary value in one table to be compared against two values in another table. If the primary value fits between the numbers then I want it to return the result in the last column.

The reason I'm asking is that I have some odd rules about how to display certain values, i.e. 7.5 can be displayed as 10, but not 8. It's weird because normally I could use rounding rules but there are too many "if, then" clauses so it's easier to just compare the primary value to a table with "hard-coded" values. I've attached a copy of the DB with your SQL statement in the query so you can see what I'm talking about.

Anyway. Thanks for the help, it's sincerely appreciated. Question solved!

[CJ_London]

Glad I guessed right

if you wanted to use this in a more complex query (such as where you have left or right joins) where Cartesians (i.e. no join) are not allowed then although possibly slower for large datasets another way this can be done is

SELECT IDItm, AmntClrs, (select top 1 DVRslt FROM DVVtmnMnrl WHERE AmntClrs between lwr and uppr) AS DVRslt
FROM 1Clrs;

I think you were trying to do the bit in red in post #4

Also ensure in your live db that lwr and uppr are indexed

[Eddy Sincere]

Nice! I'll definitely keep it in my tool box.

Thanks again for the help.