Query range of values? Tparis 03.08.2006 20:39:01 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?

RE: Query range of values? KARL DEWEY 03.08.2006 23:33:01 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. "Tparis" wrote: [Quoted Text] > 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?

Re: Query range of values? "Jeff Boyce" <nonsense[ at ]nonsense.com> 03.08.2006 23:41:03 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 "Tparis" <Tparis[ at ]discussions.microsoft.com> wrote in message news:FC0F731F-AC9F-4540-BD8B-439CE290B3A7[ at ]microsoft.com... [Quoted Text] > 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?

Re: Query range of values? John Vinson <jvinson[ at ]STOP_SPAM.WysardOfInfo.com> 03.08.2006 23:45:25 On Thu, 3 Aug 2006 13:39:01 -0700, Tparis <Tparis[ at ]discussions.microsoft.com> wrote: [Quoted Text] >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]

RE: Query range of values? Tparis 14.08.2006 16:57:16 Sorry for the confusion. I meant the the field may have data like this -- [Quoted Text] > 12345-67955 > 23454-89088 > 34511-96789 > I would enter 23500 in my parameter query and hope to get the record for 23454-89088. Thanks "KARL DEWEY" wrote: > 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. > > "Tparis" wrote: > > > 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?

RE: Query range of values? KARL DEWEY 14.08.2006 17:37:02 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. "Tparis" wrote: [Quoted Text] > 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 > > "KARL DEWEY" wrote: > > > 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. > > > > "Tparis" wrote: > > > > > 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?

Re: Query range of values? Tparis 16.08.2006 18:54:01 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:] "John Vinson" wrote: [Quoted Text] > On Thu, 3 Aug 2006 13:39:01 -0700, Tparis > <Tparis[ at ]discussions.microsoft.com> wrote: > > >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] > >