[RESOLVED] Trickish Situation With Data Range Search

[Tobyy]

Hello,
Please i need help with the following situations.
I have a database table named "tabJrecord" which contains ranges of numbers, like as shown below :

Col1	Col2	Col3	Col4
Apple	REF1	6000	6400
Banana	REF2	6500	6800
Orange	REF3	7000	8000

Now, i have a form which accepts a number, i want to be able to search if that number exist in any range of any row in the table.

For example, if a user enters number 6,300. I want to be able search the table rows and my recordset should be able to tell me the number 6,300 exist in for the Fruit "Banana". That is if the number is between Col3 and Col4 of any row.

Kindly help with the MSSQL Query for this.

Thanks.

[Arnoutdv]

select Col1, Col2 from tabJrecord where Col3 >= 6300 and Col4 <= 6300 and Col1 = 'Banana'

But given the data, there is no record Banana which matches 6300.

[Zvoni]

SELECT * FROM tabjrecord WHERE 6300 BETWEEN Col3 AND Col4

And i agree with Arnout: No Banana, but Apple instead

[vb6forever]

Why SQL ?
With DAO, just do a Inner Loop using a Seek which searches each column other than 1.
Once found read column1 value for your answer.

I agree with the other posted, in the example given the result set would be Not Found!

[Zvoni]

Why SQL ?

Because it's a Database.
And filtering your resultset on the Server-side via SQL will always beat a seek on a complete table.

EDIT: IIRC, seek only works on a single Comparator. In OP's case he has to compare the value to two Columns simultainiously with different operators
Meaning you have to do the operation twice.
Nevermind with Seek you only get one result back (the first one found). If you want all, you would have to "continue" seeking.
And seek is only "fast" on Indexed search-keys. If you search on non-indexed columns it's a sequential iteration through the table

[wqweto]

EDIT: IIRC, seek only works on a single Comparator. In OP's case he has to compare the value to two Columns simultainiously with different operators
Meaning you have to do the operation twice.
Nevermind with Seek you only get one result back (the first one found). If you want all, you would have to "continue" seeking.
And seek is only "fast" on Indexed search-keys. If you search on non-indexed columns it's a sequential iteration through the table

Seek method works only in JET and only when there is an index on the column being searched.

This is the way DBF files are used, very educational if you are used to SQL with whatever predicate you wish in the WHERE clause. Makes you think more about indexing and those abysmal tables scans.

Alas no, you cannot use Seek with MSSQL no matter if there is an index on the column or not. It's just not supported by the OLEDB provider (or any other access driver/provider) AFAIK.

cheers,
</wqw>

[Tobyy]

SELECT * FROM tabjrecord WHERE 6300 BETWEEN Col3 AND Col4

And i agree with Arnout: No Banana, but Apple instead

Thank you soooooooooooooooooooooooooooo much.
This worked PERFECTLY !