I’m trying to write a SQL statement in VBA to concatenate first and last name

I’m getting errors.

SELECT [FirstName] & " " & [LastName] AS FullName, TblMembers.Position
FROM TblMembers
WHERE (((TblMembers.Position)="Lt #1"));

This is the original….

SQL = "SELECT [FirstName] & " " & [LastName] AS FullName, TblMembers.Position
       FROM TblMembers
       WHERE (((TblMembers.Position)="Lt #1"));"

I’m exporting names to excel files and have several files, don’t want to make queries for each one, so would like to extract the data on open.

Here is my full code, corrected as below.

Private Sub Cmdtestopen_Click()


On Error GoTo SubError

    Dim xlApp As Excel.Application
    Dim xlBook As Excel.Workbook
    Dim xlSheet As Excel.Worksheet
    Dim SQL As String
    Dim rs1 As DAO.Recordset


    SQL = " SELECT [FirstName] & "" "" & [LastName] AS FullName, TblMembers.Position " & _
    " FROM TblMembers " & _
    " WHERE TblMembers.Position='Lt #1' "


    Set rs1 = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)


    If rs1.RecordCount = 0 Then
        MsgBox "No data selected for export", vbInformation + vbOKOnly, "No data exported"
        GoTo SubExit
    End If


    Set xlApp = Excel.Application

    xlApp.Visible = False
    Set xlBook = xlApp.Workbooks.Add
    Set xlSheet = xlBook.Worksheets(1)

    With xlSheet
        .Name = "Discount"
        .Cells.Font.Name = "Calibri"
        .Cells.Font.Size = 11


        Do While Not rs1.EOF

            .Range("A1").Value = Nz(rs1!FullName, "")


            rs1.MoveNext

        Loop


    End With


SubExit:
On Error Resume Next

    DoCmd.Hourglass False
    xlApp.Visible = True
    rs1.Close
    Set rs1 = Nothing

    Exit Sub

SubError:
    MsgBox "Error Number: " & Err.Number & "= " & Err.Description, vbCritical + vbOKOnly, _
        "An error occurred"
    GoTo SubExit


End Sub

Answer

Double quotes delimit a String literal. To have double quotes inside a String literal you need to “escape” them; in VBA the way to do this is to double-up the double quotes:

SQL = " SELECT [FirstName] & "" "" & [LastName] AS FullName, TblMembers.Position " & _
      " FROM TblMembers " & _
      " WHERE TblMembers.Position='Lt #1' "

Advertisement

Answer