String Variables in SQL statements

[Bob Ewers]

I'm trying to feed the WHERE portion of an SQL statement with the string stPMname. The string takes it's value from an option group result. My date variables work fine, but the string variable is treated like a parameter by SQL. See code below for reference. Can anyone suggest a solution.


Also, is there a way to have an option group return zero instead of null if there is no selection.


Private Sub PMFrame_AfterUpdate()
Dim intPMvar As Integer
Dim stPMname As String
Dim SQLstmt As String
Dim startdate As Date
Dim EndDate As Date
startdate = Me![startdate]
EndDate = Me![EndDate]
intPMvar = Me![PMFrame]
Select Case intPMvar

Case 1 'Returns xxx Total

stPMname = "xxx"
SQLstmt = "SELECT [%$##@_Alias].pm, Sum([%$##@_Alias].bps) AS SumOfbps FROM [SELECT pm, rpm_date as ddate, bps FROM rpm_data WHERE rpm_date >= startdate AND rpm_date <= enddate UNION SELECT pm, trade_date as ddate, bps FROM trade WHERE trade_date >= startdate AND trade_date <= enddate]. AS [%$##@_Alias] WHERE [%$##@_Alias].pm = stPMname GROUP BY [%$##@_Alias].pm"
PMReturn.RowSource = SQLstmt
PMReturn.Requery

 

[Dirk Goldgar]

I'm trying to feed the WHERE portion of an SQL statement with the
string stPMname. The string takes it's value from an option group
result. My date variables work fine, but the string variable is
treated like a parameter by SQL. See code below for reference. Can
anyone suggest a solution.


Also, is there a way to have an option group return zero instead of
null if there is no selection.


Private Sub PMFrame_AfterUpdate()
Dim intPMvar As Integer
Dim stPMname As String
Dim SQLstmt As String
Dim startdate As Date
Dim EndDate As Date
startdate = Me![startdate]
EndDate = Me![EndDate]
intPMvar = Me![PMFrame]
Select Case intPMvar

Case 1 'Returns xxx Total

stPMname = "xxx"
SQLstmt = "SELECT [%$##@_Alias].pm, Sum([%$##@_Alias].bps) AS
SumOfbps FROM [SELECT pm, rpm_date as ddate, bps FROM
rpm_data WHERE rpm_date >= startdate AND rpm_date <= enddate
UNION SELECT pm, trade_date as ddate, bps FROM trade WHERE trade_date

= startdate AND trade_date <= enddate]. AS [%$##@_Alias] WHERE

[%$##@_Alias].pm = stPMname GROUP BY [%$##@_Alias].pm"
PMReturn.RowSource = SQLstmt
PMReturn.Requery

Your date variables aren't actually working fine; the query interpreter
is (cleverly) just picking up the values from the form controls that
have those names. Since you're building the SQL statement as a literal
string, you have to "concatenate in" the values of the variables. Try
this:

SQLstmt = _
"SELECT T.pm, Sum(T.bps) AS SumOfbps " & _
"FROM [" & _
"SELECT pm, rpm_date as ddate, bps " & _
"FROM rpm_data " & _
"WHERE rpm_date >= " & _
Format(startdate, "\#mm/dd/yyyy\#") & _
" AND rpm_date <= " & _
Format(enddate, "\#mm/dd/yyyy\#") & _
" UNION " & _
"SELECT pm, trade_date as ddate, bps " & _
"FROM trade " & _
"WHERE trade_date >= " & _
Format(startdate, "\#mm/dd/yyyy\#") & _
" AND trade_date <= " & _
Format(enddate, "\#mm/dd/yyyy\#") & _
"]. AS T " & _
"WHERE T.pm = " & Chr(34) & stPMname & Chr(34) & _
" GROUP BY T.pm"

 

[Tim Ferguson]

SELECT [%$##@_Alias].pm,
Sum([%$##@_Alias].bps) AS SumOfbps
FROM
[ SELECT pm, rpm_date as ddate, bps
FROM rpm_data
WHERE rpm_date >= startdate
AND rpm_date <= enddate
UNION
SELECT pm, trade_date as ddate, bps
FROM trade
WHERE trade_date >= startdate
AND trade_date <= enddate
]. AS [%$##@_Alias]
WHERE [%$##@_Alias].pm = stPMname
GROUP BY [%$##@_Alias].pm"

I am a bit puzzled as to how the date variables get into this: you should
really be formatting and inserting them as literals:-

"... rpm_date >= " & Format$(startdate,"\#yyyy\-mm\-dd\#") & _
" AND rpm_date <= " & Format$(startdate,"\#yyyy\-mm\-dd\#") & _

and so on. It follows that the string values also have to be properly
delimited (watch out for embedded quote marks):-

" ... WHERE pm = """ & stPMname & """ " & _

An alternative would be to use a paremeterised query, and save it as a
querydef.

A good tip is to use a 'MsgBox SQLStmt' or 'Debug.Print SQLStmt' to check
what is actually being sent to the db engine.

Also, is there a way to have an option group return zero instead of
null if there is no selection.

Use a NZ function.


Hope that helps


Tim F