Using "Like" operator

  • Thread starter Thread starter JR
  • Start date Start date
J

JR

Hello,
I am attempting to use the like operator to determine if a
customer name (or a portion of name) exists in a table.
The user enters the name on a form in a text box then
presses a button. For some reason I can get the code to
work in a query but am unable to get the VBA code to
reconize the command (does not see similar named customer).
Any help would be appreciated.

'now determine if customer already exists
stSQL = "SELECT COUNT(*) AS AvailableRecords " & _
"FROM [dbo_tblCustomer] " & _
"WHERE ((dbo_tblCustomer.CustomerName)
Like """ & txtCustomerName & "*"");"
MsgBox stSQL

'create the connection to the current application and
Access database
Set con = Application.CurrentProject.Connection
'create a current record set from the above sql
statement
Set rst = CreateObject("ADODB.Recordset")
'open the record set using the sql statement
rst.Open stSQL, con, adOpenForwardOnly,
adLockReadOnly, adCmdText
MsgBox (rst!availablerecords)
 
Use the DCount function and Dim a variable to hold the returning number.

Dim intVar as integer
intVar = DCount("FieldToLookFor", "Table","[FieldName] Like ' " &
Me!EntryField & " *' "
Note the single quotes surrounding the input textbox reference.
 
Give this a shot. You need to use single quotes around
the text control value.

stSQL = "SELECT COUNT(*) AS AvailableRecords " & _
"FROM [dbo_tblCustomer] " & _
"WHERE dbo_tblCustomer.CustomerName " & _
"Like '*" & txtCustomerName & "*';"

Jen
 
Try this. Save the following function as a module then set the OnOpen event
property of your form to the name of the function.
Ex: OnOpen=OpenFormWithInput()

Function OpenFormWithInput()
Dim Msg As String
Dim Title As String
Dim Defvalue As String
Dim Answer As String

Msg = "Enter Part or Whole Name." & vbCrLf & "" & vbCrLf & "Or leave
blank to cancel."
Title = "Get Records with Name Like"
Answer = InputBox(Msg, Title, Defvalue)

If Answer <> "" Then
DoCmd.OpenForm "Put the name of your form here", , , "[FieldName]
like " & "'" & Answer & "' &'*'"
'Open the form and get the records with similar to input data.
Else
DoCmd.Close
End If
End Function
 
Back
Top