help with quotes while building a SQL string

[GS500]

I have the following string I want to insert into a SQL string

strFields = strFields & "DateDiff('d',[" & StrEvent1 & "],[" & StrEvent2 & "]) AS " & StrEvent1 & " to " & StrEvent2

the variables StrEvent1 and StrEvent2 hold strings containing spaces, so I'm trying to wrap the whole AS alias in quotes, but I either get an error about end of statements or end up outputting the whole literal string instead of the string in the variables.

Any help would be appreciated.

Thanks

 

[pbaldy]

Part of that doesn't make any sense, but a primary tool for figuring these types of things out is the Immediate window:

http://www.baldyweb.com/ImmediateWindow.htm

 

[GS500]

What I have is a form with field list combo boxes, for users to select from several date fields and run a query to calculate the date difference between the fields. Here is my entire sub:

Private Sub CmdCalc_Click()
'variable to hold the sql statement
Dim strSql As String
'variable to hold the selected field names
Dim strFields As String
'variables to hold the selected column names for DateDiff
Dim StrEvent1 As String
Dim StrEvent2 As String
Dim StrEvent3 As String
Dim StrEvent4 As String

'build up strFields variable
If Not IsNull(Me.cboEvent1) Then
    strFields = strFields & "[" & Me.cboEvent1.Column(1) & "]" & ","
    StrEvent1 = Me.cboEvent1.Column(1)
End If

If Not IsNull(Me.cboEvent2) Then
    strFields = strFields & "[" & Me.cboEvent2.Column(1) & "]" & ","
    StrEvent2 = Me.cboEvent2.Column(1)
End If

If Not IsNull(Me.cboEvent3) Then
    strFields = strFields & "[" & Me.cboEvent3.Column(1) & "]" & ","
    StrEvent3 = Me.cboEvent3.Column(1)
End If

If Not IsNull(Me.cboEvent4) Then
    strFields = strFields & "[" & Me.cboEvent4.Column(1) & "]" & ","
    StrEvent4 = Me.cboEvent4.Column(1)
End If
'build the DateDiff string
strFields = strFields & "DateDiff('d',[" & StrEvent1 & "],[" & StrEvent2 & "]) AS " & """" & StrEvent1 & " to " & StrEvent2 & """" & "," & _
"DateDiff('d',[" & StrEvent3 & "],[" & StrEvent4 & "]) AS " & """" & StrEvent3 & " to " & StrEvent4 & """"
'create the sql statement using the variable
'to include the user selected Fields
strSql = "SELECT ClaimID, " & strFields & " FROM qryEventDate;"
'update the querydefs
CurrentDb.QueryDefs("qryEventDate2").SQL = strSql

End Sub

When I output strSql to the Immediate window I get:

SELECT ClaimID, [Date of Loss],[Date of Report],[Initial Payment],[Date Closed],DateDiff('d',[Date of Loss],[Date of Report]) AS "Date of Loss to Date of Report",DateDiff('d',[Initial Payment],[Date Closed]) AS "Initial Payment to Date Closed" FROM qryEventDate;

So it looks like I just got the quotes sorted, and the query runs without issue. But if something else doesn't make sense let me know, I'd like to do this the right way.

 

[boblarson]

Your output should NOT have quotes around the field name

This:

SELECT ClaimID, [Date of Loss],[Date of Report],[Initial Payment],[Date Closed],DateDiff('d',[Date of Loss],[Date of Report]) AS "Date of Loss to Date of Report",DateDiff('d',[Initial Payment],[Date Closed]) AS "Initial Payment to Date Closed" FROM qryEventDate;

Should look like this


SELECT ClaimID, [Date of Loss],[Date of Report],[Initial Payment],[Date Closed],DateDiff('d',[Date of Loss],[Date of Report]) AS [Date of Loss to Date of Report],DateDiff('d',[Initial Payment],[Date Closed]) AS [Initial Payment to Date Closed] FROM qryEventDate;

 

[pbaldy]

I meant part of the code didn't make sense, but you caught/fixed it. Glad you got it sorted out.

 

[GS500]

Works great, thanks Bob and Paul.