Search for record between two fields (Access)

[David]

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

 

[Ron Hinds]

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