«

Hi,

I have a table called 'jobserial'

This contains amongst others, 2 fields called 'PSL_F_Serial' &
'PSL_L_Serial'
Both of these are 'Text' fields (VarChar) which at present hold serial
numbers

I have a form on my ASP page which loads the new serials into the
Access database as a range only, i.e. The first serial in the range
(from TEXTBOX1) loads into 'PSL_F_Serial' and the last serial in the
range (from TEXTBOX2) loads into 'PSL_L_Serial'
Only these 2 numbers are stored per record in the table.

I need an SQL statement that will check the 2 serial numbers entered
against matching serials in 'PSL_F_Serial' & 'PSL_L_Serial' and also
BETWEEN 'PSL_F_Serial' & 'PSL_L_Serial'.

i.e. if record 52 exists as :

PSL_F_Serial = 0908216206
and
PSL_L_Serial = 0908216245

so, in theory, there are 40 serial numbers including the first & last
in this range, but only the first & last are stored.

Then when the user enters a number in Textbox 1 and 2 it needs to find
out if

Text box1 = 0908216206 or 0908216245....RECORD FOUND
Text box2 = 0908216206 or 0908216245....RECORD FOUND
and
If user enters any number BETWEEN 0908216206 & 0908216245 in textbox1
or 2 ..... RECORD FOUND

Could you show me how ? thanks .... this one is really important, as
at present, duplicates are getting into the DB if the number entered
is BETWEEN the range, as I have not worked out how to catch them !

Many thanks in advance

David

"David" <> wrote in message
news:e5b52c3a-76ee-442a-a789-...
> Hi,
>
> I have a table called 'jobserial'
>
> This contains amongst others, 2 fields called 'PSL_F_Serial' &
> 'PSL_L_Serial'
> Both of these are 'Text' fields (VarChar) which at present hold serial
> numbers
>
> I have a form on my ASP page which loads the new serials into the
> Access database as a range only, i.e. The first serial in the range
> (from TEXTBOX1) loads into 'PSL_F_Serial' and the last serial in the
> range (from TEXTBOX2) loads into 'PSL_L_Serial'
> Only these 2 numbers are stored per record in the table.
>
> I need an SQL statement that will check the 2 serial numbers entered
> against matching serials in 'PSL_F_Serial' & 'PSL_L_Serial' and also
> BETWEEN 'PSL_F_Serial' & 'PSL_L_Serial'.
>
> i.e. if record 52 exists as :
>
> PSL_F_Serial = 0908216206
> and
> PSL_L_Serial = 0908216245
>
> so, in theory, there are 40 serial numbers including the first & last
> in this range, but only the first & last are stored.
>
> Then when the user enters a number in Textbox 1 and 2 it needs to find
> out if
>
> Text box1 = 0908216206 or 0908216245....RECORD FOUND
> Text box2 = 0908216206 or 0908216245....RECORD FOUND
> and
> If user enters any number BETWEEN 0908216206 & 0908216245 in textbox1
> or 2 ..... RECORD FOUND
>
> Could you show me how ? thanks .... this one is really important, as
> at present, duplicates are getting into the DB if the number entered
> is BETWEEN the range, as I have not worked out how to catch them !
>
> Many thanks in advance
>
> David

If you really mean OR here:

> If user enters any number BETWEEN 0908216206 & 0908216245 in textbox1
> or 2 ..... RECORD FOUND

use the statement below as-is. But I think what you really want is AND; if
so, just change the OR below to AND. The rest is assuming you already know
how to open a connection and a recrodset...

strSQL = "SELECT PSL_F_Serial, PSL_L_Serial FROM jobserial WHERE ('" &
TextBox1 & "'>=PSL_F_Serial AND '" & TextBox1 & "<>=PSL_L_Serial) OR ('" &
TextBox2 & "'>=PSL_F_Serial AND '" & TextBox2 & "<>=PSL_L_Serial)

rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic

If rs.EOF Then
'Record not found
Else
'Record found
End If