Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2008
    Posts
    54

    Unanswered: Problem with sql code

    Hi everyone,
    I have no problem when I click my query “SELECT [item] & " " & [description] AS CitemDesc, Sum(Table1.Qty) AS SumOfQty
    FROM Table1
    GROUP BY [item] & " " & [description];”

    Using copy and paste into Code I get syntax error when opening a form

    Private Sub Form_Open(Cancel As Integer)

    Docmd.RunSQL ("SELECT [item] & " " & [description] AS CitemDesc, & _
    Sum(Table1.Qty) AS SumOfQty FROM Table1 & _
    GROUP BY [item] & " " & [description];")

    End Sub

    Both item and description are text while qty is number. I came to realise that Copy and paste Sql from query might not work.
    Any idea why the code did not work?

    Thanks
    Mann

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Hi big mon
    I'd expect the group by to be a comma separated list
    but if the queery runs fine int he query designer then Id look elsewhere

    first off rather than use the line continuation charater I'd either have it all one one line

    Code:
    Docmd.RunSQL ("SELECT [item] & " " & [description] AS CitemDesc, sum(Table1.Qty) AS SumOfQty FROM Table1 GROUP BY [item] & " " & [description];")

    or if you prefer the way the sql is laid out at present

    Code:
    dim strSQL as string
    strsql = "SELECT [item] & " " & [description] AS CitemDesc, & _
    Sum(Table1.Qty) AS SumOfQty FROM Table1 & _
    GROUP BY [item] & " " & [description];"
    doCMD.runsql(strSQL)
    failing that I'd want to revist the group by clause
    HTH

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i made a couple of changes
    - the single quotes (fixes the SQL concatenation)
    - dropped the space in the GROUP BY (will not change the grouping and saves one (always slow) concatenation in the SQL)
    Code:
    dim strSQL as string
    strsql = "SELECT [item] & ' ' & [description] AS CitemDesc, Sum(Table1.Qty) AS SumOfQty FROM Table1 GROUP BY [item] & [description];"
    and you will probably find
    curentdb.execute strSQL
    to be faster then
    docmd.runsql(strSQL)
    ...tho both will error with a SELECT query.
    runsql & execute are both for action queries (UPDATE, INSERT etc) not SELECTs.

    probably you should explain what you are trying to achieve.

    izy
    currently using SS 2008R2

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The group by should not be concatenated
    Code:
    SELECT fisrtName & ' ' & lastName
    FROM    students
    GROUP
        BY firstName
         , lastName
    Also, if you're grouping by item and description then each entry will almost certainly be unique; therfore your aggregate function (i.e. Sum()) will probably not be doing what you want!

    But that depends what you want to achieve... Let us know!
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2008
    Posts
    54
    Thanks both of you .
    I have tried both of your method using copy and paste still I get error like “syntax error” or “cannot execute a selected query.”

    To make things simpler I have table1 (which is already unique from updating and appending from another table) consisting of item, description and qty
    My table1:
    Item Description Qty
    Hose polyester 4
    Tap double 2
    Pot 40cm dia 3

    When I click run my query directly I have no problem of getting the datasheet (where Citem is item & “ “ & description )
    my datasheet:
    Citem Qty
    Hose polyester 4
    Tap double 2
    Pot 40cm dia 3

    Using the same SQL from the query I wanted to do the same in codes but without success

    Dim db as Dao.database
    Dim strSQl as string

    strsql = "SELECT [item] & " " & [description] AS CitemDesc, & _
    'Sum(Table1.Qty) AS SumOfQty FROM Table1 & _
    'GROUP BY [item] & " " & [description];"

    DoCmd..RunSQl(strSQL)
    Or
    Currentdb.execute strSQL

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why don't you explain what you're trying to achieve?

    Chances are you want to open what's called a recordset using your SQL and manipulate the results that way...

    If so, then don't concatenate in your select - concatenate in the VBA after accessing the records.
    George
    Home | Blog

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    concatenation is still a mess.
    consider:
    someString = "Fred" "Sam"
    ...it's not going to work.
    and that is what you are trying to do to strSQL.
    now try:
    someString = "Fred ' ' Sam"

    before trying to .execute strSQL, why not have a look at it:
    strSQL = ...try it your way
    msgbox strSQL
    ...what do you see? ...nothing: you get a syntax error!

    now try:
    strSQL = ...try it my way
    msgbox strSQL
    ...what do you see?

    fine - now we have the concatenation issue handled, we are left with the problem that you can't .execute a SELECT query (see #3).

    in #5 you say you want the same effect as when you double-click a saved query. this seems a sloppy approach to UI design, but here is how you do it:

    dim db as dao.database
    dim qdef as dao.querydef
    dim strSQL as string
    set db = currentdb
    strSQL = ....do it my way, please!
    set qdef = db.createquerydef("MyNewQuery", strSQL)
    qdef.close
    set qdef = nothing
    set db = nothing
    docmd.openquery "MyNewQuery", acviewnormal

    ...that will DIE if "MyNewQuery" already exists.
    to modify the SQL of an existing query (will DIE if query does not exist!) use:

    dim db as dao.database
    dim qdef as dao.querydef
    dim strSQL as string
    set db = currentdb
    strSQL = ....do it my way, please!
    set qdef = db.querydefs("MyExistingSavedQuery")
    qdef.SQL = strSQL
    qdef.close
    set qdef = nothing
    set db = nothing
    docmd.openquery "MyExistingSavedQuery", acviewnormal

    ...but it is still sloppy UI design. better is to feed a list or form.

    izy
    Last edited by izyrider; 01-06-08 at 05:44.
    currently using SS 2008R2

  8. #8
    Join Date
    Jan 2008
    Posts
    54
    Thanks for invaluable help, all of you. I resign to the problem of coding of SQL especially with concatenation.

    DoCmd. OpenQuery “ queryname” is indeed much easier.

Posting Permissions

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