Results 1 to 9 of 9

Thread: SQL problem

  1. #1
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178

    Unanswered: SQL problem

    I create a SQL statement in VBA which translates to:
    Code:
    SELECT tblInvData.Class FROM tblInvData GROUP BY tblInvData.Class WHERE Class >= '11' ORDER BY tblInvData.Class
    The original VBA code, in the cmbFrom AfterUpdate event, is
    Code:
               With Me.cmbTo
                    .RowSource = "SELECT tblInvData.Class FROM tblInvData " _
                    & "GROUP BY tblInvData.Class WHERE Class >= '" & Me.cmbFrom.Value _
                    & "' ORDER BY tblInvData.Class "
                    .ColumnCount = 1
                    .BoundColumn = 1
                End With
    When I run the program, I keep getting an error message as follows:
    Code:
    Syntax error (missing operator) in query expression 'tblInvData.Class WHERE Class >='11".
    implying that I'm missing my closing single quote. I'm not, though, as both my VBA code and the Immediate Window results prove. What's going on?

    Class is alphanumeric, and I'm using A2002.

    Sam

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    GROUP BY comes after the WHERE clause...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Thanks, Teddy.

    Actually, I made a query first, and then copied and pasted the SQL into my VBA, where I made the the "Me......" changes.

    Sam

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Can you Debug.Print the RowSource and paste it back to a query
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  5. #5
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I'm not sure what you mean. The first line I posted was the result of a Debug.Print. What do you mean 'paste it back to a query?'

    Sam

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1

  7. #7
    Join Date
    Feb 2006
    Posts
    15
    Quote Originally Posted by Sam Landy
    I'm not sure what you mean. The first line I posted was the result of a Debug.Print. What do you mean 'paste it back to a query?'

    Sam
    He means Debug.Print the query to the Immediate Window and then copy it from the Immediate window back into a query (or a query analyzer tool or however you like) and run the query to see if it executes - but Teddy is right, check the order of your clauses ...

  8. #8
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    When all else fails... Weird! Now it comes out in the right order in VBA also. It also does what I want now.

    Thanks, everyone; and Teddy, sorry for the obstinacy. It's part of what forces me to learn accurately, and hence makes me a fair-to-good programmer, but that's not much of an excuse.

    Sam

  9. #9
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    No problems, everybody has brainfarts sometimes. I had one myself this week trying to figure out why this works to generate a comma-delimited list in SQL Server:

    SELECT @CONCAT_STR = COALESCE((@CONCAT_STR + ','), '') + ([FieldToConcat])
    FROM tblEmployee

    SELECT @CONCAT_STR

    I needed three different people to tell me I was selecting the VARIABLE at the end, not the select statement. Couldn't figure out how the hell I was getting a scalar value back...

    meh.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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