Query by height

[mxp346]

Is it possible to set up a query so that it finds all the records between two heights? I set up an input mask so that the heights are always entered like _'__".

 

[dalchri]

Since your database has enforced, structured data, you can get away with a string comparison of numbers. The real challenge is that you've got embedded single AND double quotes in your string. The following query should work:
[tt]
SELECT *
FROM YourTable
WHERE Height Between '5' & chr(39) & '02"' And '6' & chr(39) & '05"'
[/tt]
If you use query parameters, it ought to be even easier:
[tt]
PARAMETERS MinHeight Text(5), MaxHeight Text(5);
SELECT *
FROM YourTable
WHERE Height Between MinHeight And MaxHeight
[/tt]
Note that it does not matter what order MinHeight and MaxHeight are in. This query will retrieve the same results:
[tt]
PARAMETERS MinHeight Text(5), MaxHeight Text(5);
SELECT *
FROM YourTable
WHERE Height Between MaxHeight And MinHeight
[/tt]

 

[mxp346]

That was very helpful, thank you. One more question... If i want all the values to be found if there is no minimum and maximum entered, how would I do that. I've come up with

Between NZ([Forms]![frmQry]![MinHeight],"0'00") And NZ([Forms]![frmQry]![MaxHeight],"9'11")

But it's not working. I'm guessing it has something to do with the minimum and maximum as the defaults if nothing is entered. Do you know what's wrong? Thanks again for the help.

 

[dalchri]

Are you certain that [Forms]![frmQry]![MaxHeight] is returning Null when the user doesn't input a value? It may be returning a zero length string. I would try:
[tt]
Between IIf([Forms]![frmQry]![MinHeight] & "" = "",
"0'00",
[Forms]![frmQry]![MinHeight])
And IIf([Forms]![frmQry]![MaxHeight] & "" = "",
"9'11",
[Forms]![frmQry]![MaxHeight])
[/tt]
to be safe.

 

[mxp346]

That works a lot better. Thank you very much for all the help dalchri.