title: I'm trying to write a SQL statement in VBA to concatenate first and last name
body: <p>I'm getting errors.</p>
  
  <pre><code>SELECT [FirstName] &amp; " " &amp; [LastName] AS FullName, TblMembers.Position
  FROM TblMembers
  WHERE (((TblMembers.Position)="Lt #1"));
  </code></pre>
  
  <p>This is the original....</p>
  
  <pre><code>SQL = "SELECT [FirstName] &amp; " " &amp; [LastName] AS FullName, TblMembers.Position
         FROM TblMembers
         WHERE (((TblMembers.Position)="Lt #1"));"
  </code></pre>
  
  <p>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.</p>
  
  <p>Here is my full code, corrected as below.</p>
  
  <pre><code>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] &amp; "" "" &amp; [LastName] AS FullName, TblMembers.Position " &amp; _
      " FROM TblMembers " &amp; _
      " 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: " &amp; Err.Number &amp; "= " &amp; Err.Description, vbCritical + vbOKOnly, _
          "An error occurred"
      GoTo SubExit
  
  
  End Sub
  </code></pre>