Return Multiple "between This And This" Records

[TimTDP]

I have the following data in tblProductBottle

ID, ProductBottleMinLength, ProductBottleMaxLength
1, 80, 90
2, 85, 90
3, 80, 85

I want to filter this data in a query based on criteria entered on a form. The form has the unbound field: Forms!frmFindProduct!intBottleDiameter

if 87 is entered into intBottleDiameter, only record 2 should be returned
if 81 is entered into intBottleDiameter, both records 1 & 3 should be returned
if 91 is entered into intBottleDiameter, no record should be returned

What should the query be to return the correct results?

 

[pbaldy]

Why wouldn't record 1 be returned for 87? It's in the range. I would think:

ProductBottleMinLength <= Forms!frmFindProduct!intBottleDiameter AND ProductBottleMaxLength >= Forms!frmFindProduct!intBottleDiameter

 

[TimTDP]

Why wouldn't record 1 be returned for 87? It's in the range.
You are correct. My mistake!

I have tried what you suggest but it does not work. What you wrote is what I originally thought and really cannot understand why it does not work.

 

[pbaldy]

What is your SQL?

 

[pbaldy]

This works:

WHERE (((tblProductBottle.ProductBottleMinDiameter)<=[Forms]![Form1]![intBottleDiameter]) AND ((tblProductBottle.ProductBottleMaxDiameter)>=[Forms]![Form1]![intBottleDiameter]))

 

[MSAccessRookie]

Look up the BETWEEN Operator.

http://office.microsoft.com/en-us/access-help/between-and-operator-HP001032212.aspx

I think it will do what you want. Usage is like this:

ValueToTest BETWEEN FirstValueToCompare AND FirstValueToCompare