Dennis Dennis - 17 days ago 5
SQL Question

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' "
Comments