Results 1 to 7 of 7
  1. #1
    jker is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Aug 2023
    Posts
    36

    Need help with concating a sql select in vba code


    I have the folloing sql CODE BUT THE DOUBLE QUOTES ARE GIVING ME A PROBLEM when rewrite into VBA

    Sql is: SELECT F1 &"x" & F2 FROM XTABLE WILL GIVE ME A RESULT OF F1xF2

    Rewrite it into VBA format as :

    DIM mySQL1 as STRING
    mySQL1 = "select f1 &"x"& f2 from xtable"


    I get compile error on the select statement because of the double quotes.

    QUESTION: how do i compose the sql statement into a VBA acceptable format and getting the same result??

  2. #2
    xps35's Avatar
    xps35 is online now Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    229
    I am not sure what you want to do with the sql, but try:
    Code:
    mySQL1 = "SELECT f1 & 'x' & f2 FROM xtable"
    Groeten,

    Peter

  3. #3
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 11 Office 365
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    What's the result supposed to look like?
    Datatypes? What's f1 and f2?


    Code:
    mySQL1 = "SELECT """ & f1 & "x" & f2 & """ as SomeAlias FROM xtable"
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  4. #4
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    as this is the SQL server section: note that the concatenation sign in SQL server is +
    T-SQL statement:

    Code:
    select [field1] + ' x ' + [field2]
    If one of the fields is not a (n)varchar you need to convert it to (n)varchar, for example using the cast function:

    Code:
    select cast([field1] as varchar(50))

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    If you want Access VBA to build SQL to open a recordset of a linked SQLServer table, either + or & should work for concatenation, although the results can be different depending on field content. If a field is Null, the result with + concatenation will be Null.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    If a field is Null, the result with + concatenation will be Null.
    not always true, it depends on the database settings:
    Click image for larger version. 

Name:	DB_options.JPG 
Views:	15 
Size:	115.1 KB 
ID:	50758

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    I am not aware of any such property setting in Access. I have linked SQLServer tables and use of + concatenation returns Null when input is Null. Concatenation with & does not. Same as with a local table.

    This is because + is also arithmetic operator and arithmetic with Null returns Null. This carries over when used for concatenation.

    A pass-through query would possibly behave differently - I will have to try it.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 4
    Last Post: 11-10-2019, 11:10 PM
  2. Code: Group and select first records
    By cfobare79 in forum Access
    Replies: 9
    Last Post: 02-10-2015, 04:08 PM
  3. Need Multi-Select listbox code example
    By Buakaw in forum Forms
    Replies: 4
    Last Post: 08-21-2011, 08:37 PM
  4. When I select name code appear auto
    By miziri in forum Forms
    Replies: 0
    Last Post: 03-09-2010, 02:10 PM
  5. Using SELECT query within a VBA code in Access
    By championss in forum Programming
    Replies: 4
    Last Post: 10-23-2006, 05:50 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