CODEWITHSUNDEEP
sqlvbams-accessselectadodb
Keven M
Keven M

Reputation: 992

Access 2010 SELECT string

I am having difficulty building a proper 'SELECT' statement in Access 2010 VBA. I am trying to pull 3 fields from a table using an ADODB.Recordset. I have done this numerous times before with no problems, but this time I am trying to accomplish it based on a user entered number that is part of one of the field values. So whereas the full field may be T6825LZ, the user should be able to enter 6825 and the SELECT statement find the correct record.

My code thus far is:

 Dim rsTID As New ADODB.Recordset
 Dim searchTID As String
 Dim selectString As String

 searchTID = "T" & TID
 selectString = "SELECT * FROM Agents WHERE TID LIKE " & searchTID & ""

 rsTID.Open selectString, CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic

 If Not rsTID.EOF Then
       TID = rsTID.Fields("TID")
       LastName = rsTID.Fields("LastName")
       FirstName = rsTID.Fields("FirstName")
 End If

In the code above, 'TID' in the line searchTID = "T" & TID refers to the TextBox on the Access Form where the user enters the 4 digit number. TID in the selectString refers to the Field in the Agents table. A bit confusing I know, but it's what I've been given to work with :)

What is ultimately happening is that I'm getting an error on the rsTID.Open line stating No value given for one or more required parameters. This doesn't make any sense as according to MSDN all the parameters of the ADODB.RecordSet.Open statement are optional, and even if they were not, they are all present.

Can anyone please help identify the issue here, this is getting quite frustrating. :)

Thanks in advance...

Upvotes: 0

Views: 647

Answers (1)

Tim Williams
Tim Williams

Reputation: 166196

Your search term needs to be quoted, and you need to include wildcards for a LIKE search:

"SELECT * FROM Agents WHERE TID LIKE '*" & searchTID & "*'"

Upvotes: 2

Related Questions