Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003

    Unanswered: I need help for VBA in Access.

    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.


  2. #2
    Join Date
    Dec 2002
    Préverenges, Switzerland
    tnixon: thanks for your private mail
    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

    does it help?


Posting Permissions

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