WHERE clause with multiples conditions with values from the SELECT

Question

I need to edit an old Access form.

When a person enters information into a form field, a number associated to a name is found through a SQL SELECT statement.

"SELECT * FROM TPro WHERE Name = " & Chr(34) & cboPro.Value & Chr(34)

The TPro Table looks like this:

ID          Name               QtyMin        QtyMax     Percent
-------- ---------------- --------------- ----------- ----------
001     |   2x4 plank    |       0       |    500    |    10    
002     |   6x8 plank    |       0       |    500    |    5 
003     |   6x8 plank    |      501      |   2000    |    10 
004     |   6x8 plank    |      2001     |   99999   |    15
005     |   10x8 plank   |       0       |    500    |    5 

For this example let's say the Name found is "6x8 plank" and the number is 600. The number is stored in a variable called qtyCmd

Now I need to select which of the 3 "6x8 plank" rows is the correct one, I need to find the one that 600 is BETWEEN qtyMin and qtyMax so I know which percentage is the correct one to apply (in this case it would be 10).

I thought I could get all this through the original SELECT statement like that, but I get an error that it's missing something.

"SELECT * FROM TPro WHERE Name = " & Chr(34) & cboPro.Value & Chr(34) AND (qtyCmd >= QtyMin & qtyCmd <= QtyMax)

Would something like this allow me to find the correct row? or would I need something more complex in order to do this?

Thanks for your help and sorry for my lacking English.

Answer 1

Consider:

"SELECT * FROM TPro WHERE Name = '" & Me.cboPro & "' AND " & qtyCmd & " BETWEEN QtyMin AND QtyMax"

I presume you are setting a recordset object with that SQL. However, if all you want is a single value, could just use DLookup() domain aggregate function.

x = DLookup("[Percent]", "TPro", "[Name] = '" & Me.cboPro & "' AND " & qtyCmd & " BETWEEN QtyMin AND QtyMax")

And then another approach is a combobox that shows all those TPro fields in dropdown and user selects correct item then the Percent is available by reference to combobox column. But perhaps it is demanding too much of users to evaluate which range the qtyCmd value falls into.

Regardless of approach, only the TPro ID should be saved into data - unless there is possibility Percent values in TPro could be changed in future.