Results 1 to 9 of 9
  1. #1
    Autumn
    Guest

    SQL statement to get conditional records (A2K SR1)

    My table (tblData) contain a field called Name. I want to get a DAO recordset with Name that begins with L.

    I use the SQL statement :
    "SELECT * FROM tblData WHERE Name LIKE 'L*'"

    But the resultant recordset conatins only one record - I get the first record, missing the rest.

    Is there anything amiss from my SQL statement?

  2. #2
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL statement to get conditional records (A2K SR1)

    SELECT tblData.Name
    FROM tblData
    WHERE (((tblData.Name) Like "L*"));


    dave

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: SQL statement to get conditional records (A2K SR1)

    Where are you running the sql statement from? i.e., VB Code or query. Suggest the following

    SELECT *
    FROM [tblData]
    WHERE Name LIKE 'L*';

    If you are still only getting one record, check the data to ensure that there are not spaces, nulls, etc, as the first space of the data.
    Regards,

    Gary
    (It's been a while!)

  4. #4
    2 Star Lounger
    Join Date
    Feb 2002
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL statement to get conditional records (A2K SR1)

    Thanks Gray & Dave for your help.

    I did a debug.print to list out the names after executing the SQL command, all the names starting with "L" were printed, implying the SQL statement was okay. After debug,print, the form displays correctly too all the records required. If I comment out the debug. print statement, it was back to single record.

    Then I added rst.MoveLast and rst.MoveFirst after running the SQL, the form displays correctly all records with names that start with "L".

    The reason(s), anyone?

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL statement to get conditional records (A2K SR1)

    We would have to see the code that creates and uses the recordset, not just the SQL, to answer that question.
    Charlotte

  6. #6
    2 Star Lounger
    Join Date
    Feb 2002
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL statement to get conditional records (A2K SR1)

    Here is the codes, Charlotte:

    Private Sub cmdOK()

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String

    On Error GoTo TanganiErr

    ' txtName is a textbox for user's input in frmInfo
    strSQL = "SELECT * FROM tblData WHERE Name LIKE '" & txtName & "*'"
    End If

    Set db = CurrentDb()
    Set rst = db.OpenRecordset(strSQL)
    rst.MoveLast
    rst.MoveFirst

    ' frmInfo is the calling form in which txtName is found
    Set Forms("frmInfo").Recordset = rst
    Set rst = Nothing
    Set db = Nothing

    End Sub

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: SQL statement to get conditional records (A2K SR1)

    Um ... that *can't* be all the routine or it would never compile courtesy of the End If without an If Then and the missing label "TanganiErr".

    Leaving those problems aside, why are you setting the recordset property instead of just using the SQL to set the recordsource property? I suspect the problem is that you're setting the recordset property to a recordset, which would display the first record, but then you're destroying the recordset. Try it the other way (using recordsource) and see if that solves your problem. That way, you don't need the recordset object at all. If you really want to use a recordset, create a module level variable for the recordset and don't destroy it after setting the recordset property. Use the Close event of the form to destroy the recordset object instead.
    Charlotte

  8. #8
    2 Star Lounger
    Join Date
    Feb 2002
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL statement to get conditional records (A2K SR1)

    Sorry for the errors in the Sub that I sent. It is mainly due to my shoddy attempt to edit out irrelevant portion of the codes and renaming some of the variables. I re-post my problem as follows:

    I have a main form frmMain which displays all the records in my main table (the RecordSourec). I have a command button in frmMain called cmdSearch which would open up a form frmSearch where users key in search data (one of them is txtName for the field Name) for the various fields to search for the desired records. When a user press the command button cmdAccept in frmSearch, frmSearch would be closed and the searched results would be displayed in frmMain.

    The Sub Accept for cmdAccept is as follows (both the detailed SQL statement (to search for other fields) and the error trapping are left out for simplicity):



    Private Sub Accept()

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String

    strSQL = "SELECT * FROM tblData WHERE Name LIKE '" & txtName & "*'"
    strSQL = strSQL & " ORDER BY Name;"

    Set db = CurrentDb()
    Set rst = db.OpenRecordset(strSQL)

    If rst.BOF And rst.EOF Then
    MsgBox "There is no record."
    rst.Close
    Set rst = Nothing
    DoCmd.Close
    Exit Sub
    End If

    ' Populate the recordset else only the first record will be displayed
    rst.MoveLast
    rst.MoveFirst
    DoCmd.Close
    Set Forms("frmMain").Recordset = rst
    ' The rst isn't closed
    Set rst = Nothing
    Set db = Nothing

    End Sub

    The codes work except that I need to insert
    rst.MoveLast
    rst.MoveFirst
    before I close the form.


    I followed your advice as I understand it and re-code the Sub as below (let me call it Accept1:

    Private Sub Accept1()

    Dim strSQL As String

    strSQL = "SELECT * FROM tblData WHERE Name LIKE '" & txtName & "*'"
    ' Close frmSearch
    DoCmd.Close
    Forms("frmMain").RecordSource = strSQL

    End Sub


    When I ran the program, my frmMain showed only a single empty record. Did I understand your correctly, Charlotte? Where did I go wrong?

  9. #9
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: SQL statement to get conditional records (A2K SR1)

    Your revised code as posted should work, even if "Name" is actual name of field, which is not recommended. You probably need to post the complete SQL statement, not just the "Like" part, to be able to determine what the problem is. One little error in syntax could result in an empty set of records.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •