Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2010
    Posts
    39

    Unanswered: VBA run time error in search form

    Hi Guys.. I'm confused, I'm not sure what is wrong with the code I've set up on a multiple box search form. Everything else seems to be fine, but when it runs I get a run time error.. Code is as follows:

    Private Sub SEARCH_Click()

    Dim strWhere As String
    Dim lngLen As Long
    Const conJetDate = "\#mm\/dd\/yyyy\#"

    If Not IsNull(Me.CMDCLIENT) Then
    strWhere = strWhere & "([CLIENT] Like ""*" & Me.CMDCLIENT & "*"") "
    End If

    If Not IsNull(Me.DATE) Then
    strWhere = strWhere & "([ENTERDATE] >= " & Format(Me.DATE, conJetDate) & ") "
    End If
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then
    MsgBox "ENTER INFORMATION", vbInformation, "Nothing to do."
    Else

    strWhere = Left$(strWhere, lngLen)
    Me.Filter = strWhere
    Me.FilterOn = True

    End If

    The bold section appears to be the problem, however, I'm not 100% up on VBA, and I'm not sure why it isn't working! HELP! Cheers for any help that can be offered!
    End Sub

  2. #2
    Join Date
    Oct 2004
    Location
    Oxfordshire, UK
    Posts
    89
    Try this:

    Private Sub SEARCH_Click()
    Dim strWhere As String

    If Not IsNull(Me.CMDCLIENT) Then
    strWhere = strWhere & "[CLIENT] Like '*" & CMDCLIENT & "*'"
    End If

    If IsDate(Me.Date) Then
    'I've assumed that you'd like multiple conditions. If not, comment out the line below.
    If Not strWhere = "" Then strWhere = strWhere & " And "
    strWhere = strWhere & "[ENTERDATE]>=#" & Format(Me.Date, "mm/dd/yyyy") & "#"
    End If

    If strWhere = "" Then
    MsgBox "ENTER INFORMATION", vbInformation, "Nothing to do."
    Else
    With Me
    .Filter = strWhere
    .FilterOn = True
    End With
    End If
    End Sub

  3. #3
    Join Date
    Jan 2010
    Posts
    39
    Cheers, it appears I over complicated things a little! Stirling stuff!

  4. #4
    Join Date
    Jan 2010
    Posts
    39
    Ok.. now I appear to be getting this come up when I drop the combo box down, the code runs ok after, just curious

    syntax error in string query expression '([CLIENT] LIkes "*AC'

  5. #5
    Join Date
    Oct 2004
    Location
    Oxfordshire, UK
    Posts
    89
    Quote Originally Posted by colenzo View Post
    Ok.. now I appear to be getting this come up when I drop the combo box down, the code runs ok after, just curious

    syntax error in string query expression '([CLIENT] LIkes "*AC'
    Enclosing the expression in brackets isn't necessary but won't do any harm, but enclose the "Like" condition is better enclosed in single quotes. Build the expression like this if it helps:
    [CLIENT] Like
    [CLIENT] Like '*" & CMDCLIENT & "*'
    ([CLIENT] Like '*" & CMDCLIENT & "*')
    "([CLIENT] Like '*" & CMDCLIENT & "*')"
    Last edited by MyNewFlavour; 02-17-10 at 12:04.

Posting Permissions

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