Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2003
    Location
    Manchester, NH
    Posts
    57

    Question Unanswered: Syntax Error driving me crazy!

    Using Access 2007 on Vista and I keep getting the following error: Syntax error(missing operator) in query expression '[Contacts]=Jon Smith And [Status]=Completed And [Completed Date] Between #12/26/2010# And #12/31/2010#'


    I have looked over the code so many times and I think my brain is just missing it now. Any help would be very much appreciated! See code below!


    Private Sub Command20_Click()
    On Error GoTo Err_Command20_Click

    Dim stDocName As String
    Dim stWho As String
    Dim stDates As String
    Dim blnTrim As Boolean

    If Not IsNull(Me.Combo29) Then
    stWho = "[Contacts]=" & Me.Combo29 & " And "
    blnTrim = True
    End If

    If Not IsNull(Me.Combo32) Then
    stWho = stWho & "[Status]=" & Me.Combo32 & " And "
    blnTrim = True
    End If

    If IsNull(Me.Text10) And Me.Text10 = "" Then
    If Not IsNull(Me.Text12) And Me.Text12 <> "" Then
    stWho = stWho & "[Start Date] <=" & Me.Text12 & "#"
    blnTrim = False
    End If
    Else
    If IsNull(Me.Text12) And Me.Text12 = "" Then
    If Not IsNull(Me.Text10) And Me.Text10 <> "" Then
    stWho = stWho & "[Completed Date]>=" & Me.Text10
    blnTrim = False
    End If
    Else
    If (Not IsNull(Me.Text10) And Me.Text10 <> "") And (Not IsNull(Me.Text12) Or Me.Text12 <> "") Then
    stWho = stWho & "[Completed Date] Between #" & Me.Text10 & "# And #" & Me.Text12 & "#"
    blnTrim = False
    End If
    End If
    End If

    If blnTrim Then
    stWho = Left(stWho, Len(stWho) - 5)
    End If
    stDocName = "rptTipsLog"
    DoCmd.OpenReport stDocName, acPreview, , stWho
    Exit_Command20_Click:
    Exit Sub

    Err_Command20_Click:
    MsgBox Err.Description
    Resume Exit_Command20_Click

    End Sub

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by dalynching View Post
    Using Access 2007 on Vista and I keep getting the following error: Syntax error(missing operator) in query expression '[Contacts]=Jon Smith And [Status]=Completed And [Completed Date] Between #12/26/2010# And #12/31/2010#'
    you need to put quotes around your string values

    ... [Contacts]='Jon Smith' And [Status]='Completed' And ...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2003
    Location
    Manchester, NH
    Posts
    57

    Question Just tried the quotes like this....

    If Not IsNull(Me.Combo29) Then
    stWho = "[Contacts]=" & 'Me.Combo29' & " And "
    blnTrim = True
    End If

    and it wont even allow me to save the code. Then I tried this:

    If Not IsNull(Me.Combo29) Then
    stWho = "[Contacts]=" & "'Me.Combo29'" & " And "
    blnTrim = True
    End If

    Which did donthing but treat the field name like it was just text 'Me.Combo29'

    I know I'm missing something here, but its been a while since I have used Access like this. Also, does it matter that this is an Event Procedure on a button on a form that is supposed to open a report based on the parameters of this form?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    change this --
    Code:
    stWho = "[Contacts]=" & Me.Combo29 & " And "
    to this --
    Code:
    stWho = "[Contacts]='" & Me.Combo29 & "' And "
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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