Using Variables in VBA SQL (1 Viewer)

[Steve400]

I'm trying to add a variables into the below SQL to make up a field but can't work out how to do it.
Variable3 needs to be the field made up of text from Variable1 and Variable2.
Variable1 and Variable2 come from Combo boxes where Variable1 will be the Table name and Variable2 will be the field name.
I would love some help please.


Private Sub DesignMelbourne_Click()
Dim dbs As Database
Dim rs As Recordset
Dim qdf As QueryDef
Dim productName As String
Dim strSql As String
Set dbs = CurrentDb()
Dim Variable1 As String
Dim Variable2 As String
Dim Variable3 As String
Dim Low As String

Variable1 = Me.Combo1
Variable2 = Me.Combo2
Variable3 = Variable1 & "." & Variable2



strSql = "SELECT Services.TradingName, '" & Variable3 & "' AS [500k to 1mill] _

"FROM (((((((Services INNER JOIN Regions ON Services.TradingName = Regions.TradingName) LEFT JOIN LegalEntityID ON Services.TradingName = LegalEntityID.TradingName) LEFT JOIN Design ON Services.TradingName = Design.TradingName) LEFT JOIN OperationalRegion ON Services.TradingName = OperationalRegion.TradingName) LEFT JOIN CivilRegion ON Services.TradingName = CivilRegion.TradingName) LEFT JOIN CableRegion ON Services.TradingName = CableRegion.TradingName) LEFT JOIN Preparatory ON Services.TradingName = Preparatory.TradingName) LEFT JOIN ProjectMgt ON Services.TradingName = ProjectMgt.TradingName " & vbCrLf & _
"WHERE (((Regions.Melbourne)=""Yes"") AND (([Services]![Design and Validation])=""Yes""));"


Set rs = dbs.OpenRecordset(strSql, dbOpenSnapshot)
With dbs
Set qdf = .CreateQueryDef("tmpProductInfo", strSql)
DoCmd.OpenQuery "tmpProductInfo"
.QueryDefs.Delete "tmpProductInfo"
End With
dbs.Close
qdf.Close
End Sub

 

[Galaxiom]

Drop the single quotes from around Variable3. You are referring to a fieldname not a string value.

 

[daievans]

Having just been wrestling with the concept myself recently, this place is a great resource as is Access and SQL