Results 1 to 6 of 6
  1. #1
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    Unanswered: Missing operator in sql statement

    I have the following code in a sub and I keep getting the following error when the code gets to the set myrecordset line
    here is the code
    Dim myfirstbegin, myfirstend, mylastbegin, mylastend, strs As String, thedate
    Dim nomyage, ten, twenty, thirty, forty, fifty, sixty, seventy, eighty, ninety, hun, myage
    myfirstbegin = Nz(Me!FirstBeginningDate, #1/1/1990#)
    myfirstend = Nz(Me!FirstEndingDate, #12/31/2021#)
    mylastbegin = Nz(Me!LastBeginningDate, #1/1/1990#)
    mylastend = Nz(Me!LastEndingDate, #12/31/2021#)
    thedate = Date

    strs = "SELECT Count(tblClient.Number) AS CountOfNumber, tblClient.FirstVisit, tblClient.LastVisit, " _
    & "DateDiff('yyyy',[DOB],#" & thedate & "#) AS myAge " _
    & "FROM tblClient " _
    & "WHERE tblClient.Gender = 'Male' " _
    & "GROUP BY tblClient.FirstVisit, tblClient.LastVisit, DateDiff('yyyy',[DOB]," & thedate & ") AS Age " _
    & "HAVING tblClient.FirstVisit Between #" & myfirstbegin & "# And # " & myfirstend & "# " _
    & "AND tblClient.LastVisit Between #" & mylastbegin & "# And #" & mylastend & "#"
    the erroryntax error (missing operator) in query expression 'DateDiff('yyyy',[DOB],#" & thedate & "#) AS myAge'

    and it happens her:

    Set MyRecordset = MyDb.OpenRecordset(strs, dbOpenDynaset, dbSeeChanges)


    I am parsing in the 'thedate' variable because for some reason MDE files do not process certain functions in hardcoded SQL statements - one of these being Date() function. Unless of course you can tell me why MDB's will run Date functions in SQL statements but MDE's won't. Anyway I cannot figure out why this is not working and I am pretty good at parsing string and sql string statments.
    Dale Houston, TX

  2. #2
    Join Date
    Aug 2008
    Posts
    7
    you are using a continuation character "_" and a concatenation character "&".

    use:

    strs = "SELECT Count(tblClient.Number) AS CountOfNumber, tblClient.FirstVisit, tblClient.LastVisit, "

    strs = strs & "DateDiff('yyyy',[DOB],#" & thedate & "#) AS myAge "

    to concatenate properly.

    OR, remove the concatenation and make sure everything is properly separated by spaces.

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Nothing wrong with the way he's concatenating.

    Can you debug and stop on the Set command, and paste exactly what strs is set to here?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Your HAVING clause should become part of the WHERE clause.
    Code:
    SELECT Count(clients.number) As [count_of_number]
         , clients.first_visit
         , clients.last_visit
         , DateDiff('yyyy', client.dob, #1/1/1990#) As [age]
    FROM   clients
    WHERE  clients.gender = 'Male'
    AND    clients.first_visit BETWEEN #1/1/1990# AND #1/1/2008#
    AND    clients.last_visit  BETWEEN #1/1/1990# AND #1/1/2008#
    GROUP
        BY clients.first_visit
         , clients.last_visit
         , DateDiff('yyyy', client.dob, #1/1/1990#)
    Run the query from the query window, then transalte into VBA.
    George
    Home | Blog

  5. #5
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    I was on a very pressed timeline to finish this , so instead of trying to reolve (which I prefer to do) I was forced to omit the code I was using and write all of the criterai data to a temp table and then update the [DOB] field with DAO code.

    It worked but it still does not resolve two issues I am curious abouit

    1)Why does runtime access not execute certain functions in SQL that the full Access does?
    2) WHY did that code not run using the concatenation i had - it seemed very valid

    Anyway - I will get back to this in my free time and contqact you guys if I cannot resolve

    Thanks guys.
    Dale Houston, TX

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I can assure you that the runtime version will execute that function (as will an MDE), and the concatenation method was fine. It's also how I do it. If you have a machine not recognizing basic functions, it's usually a reference problem.

    Not sure it was your problem, but I'd point out that you didn't surround the date value with # in one of your DateDiff's. As previously noted, a Debug.Print on the string may have highlighted the problem.
    Paul

Posting Permissions

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