Query between values

[Peps]

Hi, I'm having a bit of a brain storm

I have a really easy table with only 3 fileds [Name], [From], [To]. The name filed is text, while fileds From and To are numbers.

Lets say I have a couple of entrys like the ones below:

Name From To
John 1 20
Simon 70 90

I have in a form two text boxes [From] and [To], what I
would like is to be able to return the [Name] value when the user inputs values between the [From] and [To] fileds:

I.E.
Input

From To Returns
12 18 John
1 10 John
71 85 Simon


I'm familiar with the Between function but I'm not sure how to build a query with these conditions.

Peps

 

[FoxProProgrammer]

Hi Peps,

I don't think you can use the Between clause in the Query because it is designed to retrieve records when one specific field is between two numbers. In this case, you need to retrieve records that are between two fields. The following SQL should work. You will just have to rename your text boxes accordingly.

SELECT Table1.Name FROM Table1
WHERE (([From]<=Fnum) AND ([To]>=Tnum));

While you can probably name a field "From", it is less ambiguous to name it something else since FROM is a keyword in Queries. You would enter Fnum and Tnum on a form or let Access ask you when you run the Query.


dz
dzaccess@yahoo.com

 

[FoxProProgrammer]

One point of clarification. You should use the following syntax to specify that the data is in a field on your form. Is the form bound or unbound? I assumed that it was unbound, which is why I recommended that you not name the fields on the form the same as those in your table.

SELECT Table1.Name FROM Table1
WHERE (([From]<=Forms![i]YourFormName[/i].Fnum) AND ([To]>=Forms![i]YourFormName[/i].Tnum));

Best regards,


dz
dzaccess@yahoo.com

 

[Peps]

Thanks Dz,

It's working a dream. Great stuff.

Best regards, Peps