Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2012
    Posts
    8

    Unanswered: Syntax Error (missing operator) in query expression

    I am trying to create a DCount Public Function in which the query counts the records from ID-QC in the table Contact Tracker where the filed WSFM_STAFF says TIM SCHULTZ between the varabile FromDate () and ToDate(). When i try to run the code, I get the message "Syntax Error (missing operator) in query expression. The code is below.

    Public Function SchultzQCs() As Long
    On Error GoTo err_label
    Dim temp As Long

    temp = DCount("[ID-QC]", "[CONTACT TRACKER]", 'WSFM_STAFF = 'TIM SCHULTZ' And QCDATE >= #" & varFromDate & "# AND QCDATE <= #" & varToDate & "#")
    SchultzQCs = temp

    err_exit:
    Exit Function

    err_label:
    MsgBox (Err.Description)
    GoTo err_exit
    End Function

    HERE'S THE THING: This code was written for me when I had some database work done for me, and the developer created the following Public Function below:

    Public Function RecruitPostCard() As Long
    On Error GoTo err_label
    Dim temp As Long

    temp = DCount("[ID-APP FLOW LOG]", "[HR APPLICANT FLOW DATA TABLE]", "RECRUITING = 'POST CARD' AND APP_DATE >= #" & varFromDate & "# AND APP_DATE <= #" & varToDate & "#")
    RecruitPostCard = temp

    err_exit:
    Exit Function

    err_label:
    MsgBox (Err.Description)
    GoTo err_exit
    End Function

    You'll notice that all I did was change the tables being searched, and the criteria, but didn't change any of the operators, so it is identical to what he did, except that his runs with no errors, and mine doesn't.

    CAN ANYONE HELP!!!!!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    You'll notice that all I did was change the tables being searched, and the criteria, but didn't change any of the operators, so it is identical to what he did, except that his runs with no errors, and mine doesn't.
    no looking at the posted code you inserted an additional apostrophe in place of the quote mark
    try
    Code:
     temp = DCount("[ID-QC]", "[CONTACT TRACKER]", "WSFM_STAFF = 'TIM SCHULTZ' And QCDATE >= #" & varFromDate & "# AND QCDATE <= #" & varToDate & "#")
    had you assigned the sql to a variable and then examined that variable you would have found tracking the problem easier
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    In my experience, the error message "Syntax Error (missing operator) in query expression" seldom actually has to do with a 'missing operator,' but is simply the way Access responds to any problem with the syntax of a SQL statement. Access is not known for the accuracy of its error reporting!

    And BTW, that was an exceptionally good catch, healdem!

    Linq ;0)>

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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