to check range of data

[capik79]

Hi Guys,
Need your help on this problem.
Let say table name call tbl_range
I have this set of record :-
No1 No2
1000 2000
3000 4000
5000 6000

My problem, i want to check if user insert another set no No1 : 1500 No2 :
2500. So, this means that range already clash with another range. If this
happened that insert cannot be done.

Regards,
Shaffiq

[Dan Bracuk]

If your db supports the between keyword, you can do this:

select count(No1) alreadythere
from yourtable
where #var1# between No1 and No2
or #var2# between No1 and No2

<cfif yourquery.alreadythere is 0>
insert your record
<cfelse>
do something else

If not use <= and <= in your query.

[capik79]

Thx Dan,
Anyway, I already solve the problem using between. Here my code

Select No1, No2
From table
Where (#varNo1# between NO1 and NO2)

Then in RecordCount eq 0, I proceed to insert the new record.

Really thx for your reply. Thx Dan

Regards,
Shaffiq