Search for record between two fields (Access)

Discussion in 'ASP General' started by David, Sep 5, 2008.

  1. David

    David Guest

    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, Sep 5, 2008
    #1
    1. Advertising

  2. David

    Ron Hinds Guest

    "David" <> wrote in message
    news:...
    > 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
     
    Ron Hinds, Sep 5, 2008
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?SnVzdGlu?=

    Retrieving Record Key while creating the record.

    =?Utf-8?B?SnVzdGlu?=, Oct 4, 2004, in forum: ASP .Net
    Replies:
    4
    Views:
    665
    =?Utf-8?B?SnVzdGlu?=
    Oct 5, 2004
  2. Michael Herman \(Parallelspace\)
    Replies:
    0
    Views:
    585
    Michael Herman \(Parallelspace\)
    Dec 28, 2003
  3. Abby Lee
    Replies:
    5
    Views:
    474
    Abby Lee
    Aug 2, 2004
  4. Replies:
    1
    Views:
    98
  5. AMT2K5
    Replies:
    1
    Views:
    216
    Eric Schwartz
    Nov 8, 2005
Loading...

Share This Page