Query range of values?

[Guest]

If a field contains a range of values (example: 12345-67890). I have a
parameter query which needs to pull this record if the number entered falls
in the middle of the range (example: pull all records for 12348). Can this
be done? if so, how?

 

[Guest]

I am not sure what you are asking. Do you want to enter a single value at
the prompt or a range of values?

For a single value just use this for criteria --
[Enter value to pull records]

For a range use this --
Between [Enter lowest value] And [Enter highest value]

If you meant the the field may have data like this --
12345-67955
23454-89088
34511-96789
Then give an example of what you would want to pull.

 

[Jeff Boyce]

Are you saying that a single record's field holds a "range of values"? That
is, holds the literal string "12345-67890"?

If so, your data structure needs a bit of normalizing.

You could probably create your own functions to determine the high and low
points of the range, and compare a test value to that calculated range, ...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Vinson]

If a field contains a range of values (example: 12345-67890). I have a
parameter query which needs to pull this record if the number entered falls
in the middle of the range (example: pull all records for 12348). Can this
be done? if so, how?

Your field is not "atomic". A field should contain only one value!

If you need a range, a better design would be to have two fields, Low
and High, with 12345 and 67890 respectively. You could then use a
criterion

WHERE [Low] <= [Enter target:] AND [High] >= [Enter target:]

With your current composite field you'll need a more complex, and much
less efficient query like

WHERE Val(Left([range], InStr([range],"-")-1)) <= [Enter target:]
AND Val(Mid([range], InStr([range],"-")+1)) >= [Enter target:]


John W. Vinson[MVP]

 

[Guest]

Sorry for the confusion. I meant the the field may have data like this --

12345-67955
23454-89088
34511-96789
I would enter 23500 in my parameter query and hope to get the record for 23454-89088.

Thanks

I am not sure what you are asking. Do you want to enter a single value at
the prompt or a range of values?

For a single value just use this for criteria --
[Enter value to pull records]

For a range use this --
Between [Enter lowest value] And [Enter highest value]

If you meant the the field may have data like this --
12345-67955
23454-89088
34511-96789
Then give an example of what you would want to pull.

If a field contains a range of values (example: 12345-67890). I have a
parameter query which needs to pull this record if the number entered falls
in the middle of the range (example: pull all records for 12348). Can this
be done? if so, how?

 

[Guest]

Your field does not contain number and can not be treated as such. Your
field is a text field. You will need to enclose the digits in quotes.
If you enter > "23500" you will get 23454-89088 and 34511-96789 as they are
higher ASCII characters.
If you enter > "23500" AND < "3" you will get 23454-89088 and not
34511-96789.

Sorry for the confusion. I meant the the field may have data like this --

12345-67955
23454-89088
34511-96789
I would enter 23500 in my parameter query and hope to get the record for 23454-89088.

Thanks

I am not sure what you are asking. Do you want to enter a single value at
the prompt or a range of values?

For a single value just use this for criteria --
[Enter value to pull records]

For a range use this --
Between [Enter lowest value] And [Enter highest value]

If you meant the the field may have data like this --
12345-67955
23454-89088
34511-96789
Then give an example of what you would want to pull.

If a field contains a range of values (example: 12345-67890). I have a
parameter query which needs to pull this record if the number entered falls
in the middle of the range (example: pull all records for 12348). Can this
be done? if so, how?

 

[Guest]

Thankyou for the information. Now I'm stuck once more.

The values may have a letter so I've created the High & Low fields as text
data types.

How would I ned to adjust this statement to work for text fields?

WHERE [Low] <= [Enter target:] AND [High] >= [Enter target:]

If a field contains a range of values (example: 12345-67890). I have a
parameter query which needs to pull this record if the number entered falls
in the middle of the range (example: pull all records for 12348). Can this
be done? if so, how?

Your field is not "atomic". A field should contain only one value!

If you need a range, a better design would be to have two fields, Low
and High, with 12345 and 67890 respectively. You could then use a
criterion

WHERE [Low] <= [Enter target:] AND [High] >= [Enter target:]

With your current composite field you'll need a more complex, and much
less efficient query like

WHERE Val(Left([range], InStr([range],"-")-1)) <= [Enter target:]
AND Val(Mid([range], InStr([range],"-")+1)) >= [Enter target:]


John W. Vinson[MVP]