SQL statement to get conditional records (A2K SR1)

Topic

New Reply

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?

Reply | Quote

Replies

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

dave

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote