Results 1 to 7 of 7
  1. #1
    Juan4412 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2010
    Posts
    209

    Dynamically Build Query String

    I am building a query string through VBA and the only issue I can not over come is how to NOT have a trailing comma at the end when it is the last range.
    This is my syntax


    Code:
        strSQL = "Select [helpTable].[userID], "
            For index1 = 1 To countOfFieldsToAdd
                strSQL = strSQL & "Cstr(icc4) AS Item_" & index1 & ", Cstr(qcc42) AS Qty_" & index1 & ",'" & masochine & "',"
            Next index1
        strSQL = strSQL & "INTO [TableForExport] FROM helpTable ORDER BY [helpTable].[userID]"
        Debug.Print strSQL
    And the issue is that when I reach the last index1 there is a comma placed after masochine so the syntax reads masochine, INTO which of course throws an error. How can I account for the LAST iteration and not add a comma at the end of masochine if it is hte last iteration?

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    strSQL = Left(strSQL,Len(strSQL)-1) & " INTO...

    You don't have spaces after the comma so watch out for that, you will at least need a space before INTO.

  3. #3
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Try this:
    Code:
        strSQL = "Select [helpTable].[userID], "
            For index1 = 1 To countOfFieldsToAdd
                strSQL = strSQL & "Cstr(icc4) AS Item_" & index1 & ", Cstr(qcc42) AS Qty_" & index1 & ",'" & masochine & "',"
            Next index1
        strSQL = Left(strSQL,Len(strSQL)-1) & " INTO [TableForExport] FROM helpTable ORDER BY [helpTable].[userID]"
        Debug.Print strSQL
    Note that in addition to remove that comma, you will want a space before the word "INTO" (which I showed above).


    EDIT: Looks like aytee111 exposed my slow typing skills and posted in the time I was crafting this response!

  4. #4
    Juan4412 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2010
    Posts
    209
    Excellent Ideas! Thanks to the both of you!

  5. #5
    Juan4412 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2010
    Posts
    209
    Hmm...now when I run the syntax through VBA the table is not created and I have a table created showing an error of Could not save the object

    The table name is called Name AutoCorrect Save Failures

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Have never had to delve into this area, but have seen it written that people switch off these options because it causes errors. It is under Current Database>Name AutoCorrect

  7. #7
    Juan4412 is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2010
    Posts
    209
    @aytee111 that solved it, thank youl.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. VBA Query Build
    By jo15765 in forum Programming
    Replies: 4
    Last Post: 03-20-2017, 08:14 AM
  2. Call a Function to build a string
    By tgall in forum Queries
    Replies: 1
    Last Post: 03-24-2016, 10:12 AM
  3. Dynamically update an append query
    By clancy76 in forum Queries
    Replies: 3
    Last Post: 02-02-2016, 08:48 AM
  4. Replies: 2
    Last Post: 01-10-2016, 06:47 PM
  5. Form to dynamically run query with 'OR' clause
    By rhewitt in forum Programming
    Replies: 9
    Last Post: 10-24-2012, 01:24 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums