Hi, I'm having problems with the "Select" statment in MS Access. I'm trying to put code into a command button that will find an search for * LastNames ending with whatever the user puts in the Text box called txtFind. I have just one table in the database called tblInfo. Can someone help me please.
Hi, when I tried to use this statment in VBA in Access I got a complied run time error '2465' Application- defined or object defined error. Here is the code I used:
Dim strSQL As String
strSQL = "SELECT * FROM tblInfo WHERE LastName Like ""*"
strSQL = strSQL & Forms!frmInfo.txtFind.Value & "" * ""
& "" * "" at the very end is where it gets lost
for a text field, SQL is expecting the string to look like
WHERE fieldName Like "someTextInQuotes";
to get the quotes into the string you can use my (actually m.timoney's) "" (which translates to a single " inserted into the string) or you can use RNG's more traditional ' (which does exactly the same thing but is tougher to read when you have combinations like '" or "'").
so the construction is:
strSQL = strSQL & "WHERE FieldName Like ""*"
'your string now reads: WHERE FieldName Like "*
strSQL = strSQL & Forms!frmInfo.txtFind.Value
'if the text box contains "Fred"
'your string now reads: WHERE FieldName Like "*Fred
strSQL = strSQL & "*"";"
'this bit adds: *";
'your string now reads: WHERE FieldName Like "*Fred*";
'which is maybe what you wanted
...though your original question was asking for Like "*Fred"; so the last bit should instead read:
strSQL = strSQL & """;"
'leaving your string: WHERE FieldName Like "*Fred";
in both cases of this last bit:
the first " opens a literal string
the next "" inserts one double-quote into the literal string
the ; signals the end of the SQL query
the last " closes the literal string
using your & "" * "" does the following:
strSQL & ""
'adds an empty string to strSQL, then
strSQL * ""
'attempts to multiply strSQL by an empty string