Results 1 to 13 of 13
  1. #1
    chalupabatman is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    114

    Properly Quote Variables In SQL String

    I am using variables in a SQL String to build an Access Query - my issue is (I Think) the fact that when I do a Debug.Print sql2 this is the output that I get


    Code:
    SELECT DISTINCT [_SpreadsheetImport].' & locID & ', ConcatRelated('& userID & ','[_SpreadsheetImport]',' & locID & ' =' & locID & ') AS ItemCode,ConcatRelated('&  saleCT &','[_SpreadsheetImport]',' & locID & ' =' &  locID & ') AS ItemQty INTO [TotalInfo] FROM [_SpreadsheetImport]
    So I am actually printing the variable name, and not the value. This is the syntax that I have, should I Have used double quotes instead of single quotes around the variables in the string? What is the proper way to escape these characters? Despite the name, they are all string variables

    Code:
    Dim sql2 As String
    sql2 = "SELECT DISTINCT [_SpreadsheetImport].' & locID & ', ConcatRelated('& userID & ','[_SpreadsheetImport]',' & locID & ' =' & locID & ') AS ItemCode,ConcatRelated('&  saleCT &','[_SpreadsheetImport]',' & locID & ' =' &  locID & ') AS ItemQty INTO [TotalInfo] FROM [_SpreadsheetImport]"
    Debug.Print sql2

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    More like:

    ...[_SpreadsheetImport].'" & locID & "', ConcatRelated...
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    chalupabatman is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    114
    Quote Originally Posted by pbaldy View Post
    More like:

    ...[_SpreadsheetImport].'" & locID & "', ConcatRelated...
    I added double quotes inside and outside each of teh & and my syntax is
    Code:
    sql2 = "SELECT DISTINCT [_SpreadsheetImport].'" & locID & '", ConcatRelated('" & userID & '",'[_SpreadsheetImport]','" & locID & '" ='" & locID & '") AS ItemCode,ConcatRelated('" &  saleCT & '",'[_SpreadsheetImport]','" & locID & '" ='" &  locID & '") AS ItemQty INTO [TotalInfo] FROM [_SpreadsheetImport]"
    But now I get a syntax error when I compile

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Always quotes surrounding literal text:

    "whatever" & variable & "..." & AnotherVariable & "..."

    You have the single and doubles reversed when restarting.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    chalupabatman is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    114
    Ah, I thought it went single, double on both ends.

    So remedying that removes the syntax error, but now when I go to actually execute the query I get an error of
    Invalid use of '.', '!', or '()' in query expression '[_SpreadsheetImport].'[NA8523]'

    It seems that maybe my first variable does not need to be encompassed with the single quotes? Maybe...

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What does the debug.print show now? If that first resolves to a field name, correct, no single quotes around it.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    chalupabatman is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    114
    Yes the 1st resolves to a field name. Good catch...if I copy/paste the Debug.Print into a SQL View of query and remove the single quotes around the 1st field it runs.

    So should my syntax be
    Code:
    [_SpreadsheetImport].' & locID & '
    Obviously not the above, bc if I do that then the Debug.Print just prints the variable name and not the value.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    No, double quotes to separate literal text from a variable:

    [_SpreadsheetImport]." & locID & "
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    This tutorial may be of interest:

    http://www.baldyweb.com/BuildSQL.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    chalupabatman is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    114
    My debug.print produces the below query
    Code:
    SELECT DISTINCT [_SpreadsheetImport].[locID], ConcatRelated([VID],'[_SpreadsheetImport]',[locID] =[locID]) AS ItemCode,ConcatRelated([PLS],'[_SpreadsheetImport]',[locID] =[locID]) AS ItemQty INTO [TotalInfo] FROM [_SpreadsheetImport]
    but when I try to execute it I get an error of Unknown Access database engine error

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Try without the ConcatRelated() fields. If those are Allen's, they need quotes around the arguments:

    http://allenbrowne.com/func-concat.html
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    chalupabatman is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    114
    Quote Originally Posted by pbaldy View Post
    Try without the ConcatRelated() fields. If those are Allen's, they need quotes around the arguments:

    http://allenbrowne.com/func-concat.html

    Yes those are Allen's. I added in the single quotes, and now I get an error of unknown access database engine error?

    This is what my debug.print produces
    Code:
    sql2 = "SELECT DISTINCT [_SpreadsheetImport]." & locID & ", ConcatRelated('" & userID & "','[_SpreadsheetImport]','" & locID & "' ='" & locID & "') AS ItemCode,ConcatRelated('" &  saleCT & "','[_SpreadsheetImport]','" & locID & "' ='" &  locID & "') AS ItemQty INTO [TotalInfo] FROM [_SpreadsheetImport]"

  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The debug should have output the finished SQL, not that. Can you attach the db here?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 5
    Last Post: 01-14-2015, 08:16 PM
  2. Quote Database
    By jimbob in forum Database Design
    Replies: 1
    Last Post: 03-17-2014, 05:14 PM
  3. Replies: 5
    Last Post: 12-22-2012, 01:36 PM
  4. Replies: 3
    Last Post: 07-26-2012, 03:09 PM
  5. Replies: 3
    Last Post: 04-23-2012, 12:06 AM

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