Trouble with SQL String in VBA

[Manuel]

I have a table which stores SQL statements as strings. I'm trying to filter
the table for a particular SQL string in VBA.

The code fails at: Set rst = db.OpenRecordset(strSQL). I get Run-time
error: 3075. The error message indicates that I'm missing an operator.

The code is below. The fstrWhere variable returned by the GetWhereClause
function equals:

"WHERE [UserID]= 'KZ1' AND ([ActualSaleDt] Between #8/1/2008# And #8/31/2008#)

Function CkFiltExists(bln As Boolean, FiltNm As String)

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim i As Integer
Dim strSQL As String
Dim fstrWhere As String

Call GetWhereClause(fstrWhere)

strSQL = "SELECT * From tbl_UserFilters WHERE (([WhereClause]) = '" &
fstrWhere & "')"

Set db = CurrentDb

Set rst = db.OpenRecordset(strSQL)

i = rst.RecordCount

If i > 0 Then
bln = True
FiltNm = rst("ShortDesc").Value
Else
bln = False
End If

rst.Close
End Function

In the immediate window, strSQL contains:

SELECT * From tbl_UserFilters WHERE (([WhereClause]) = 'WHERE [UserID]=
'KZ1' AND ([ActualSaleDt] Between #8/1/2008# And #8/31/2008#)'

Anyone know what I'm doing wrong.

Thanks,
Manuel

 

[Tom van Stiphout]

On Tue, 11 Mar 2008 16:12:06 -0700, Manuel

You're doubling up on the "WHERE" keyword.

To debug issues like this, set a breakpoint and get the value of your
sql statement. Then paste it into a new query (in SQL view). Switch to
Design view and the parser will bark at you.

-Tom.

 

[Sylvain Lafontaine]

Probably that you need to duplicate the single quote ' around KZ1; something
like:

strSQL = "SELECT * From tbl_UserFilters WHERE (([WhereClause]) = '" &
Replace (fstrWhere, "'", "''") & "')"


Another possibility would be to use the double quote as your string
delimiter:

strSQL = "SELECT * From tbl_UserFilters WHERE (([WhereClause]) = """ &
fstrWhere & """)"

 

[Steve Sanford]

Manuel,

It looks like the problem is with the string delimiters.

In the immediate window, strSQL contains:

SELECT * From tbl_UserFilters WHERE (([WhereClause]) = 'WHERE [UserID]=
'KZ1' AND ([ActualSaleDt] Between #8/1/2008# And #8/31/2008#)'

*You have single quotes within single quotes. There is a single quote in
front of "WHERE and at the end of the string as well as single quotes
embedded in the string around KZ1.

There are two ways to solve this. The first is to use doubled up double
quotes - it looks like this:

strSQL = "SELECT * From tbl_UserFilters WHERE (([WhereClause]) = """ &
fstrWhere & """)"

Expanded it is ...(([WhereClause]) = " " " &

and

& " " " ) "


The second way is to use the CHR() function. This is easier to read. It
looks like this:

strSQL = "SELECT * From tbl_UserFilters WHERE (([WhereClause]) = " & Chr(34)
& fstrWhere & Chr(34) & ")"

CHR(34) is the ASCII for the double quote.

Here is you code - modified to use the CHR() function.

'----------code beg --------------
Function CkFiltExists(bln As Boolean, FiltNm As String)

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim i As Integer
Dim strSQL As String
Dim fstrWhere As String

Call GetWhereClause(fstrWhere)

'CHR(34) is ASCII " (double quote)
strSQL = "SELECT * FROM tbl_UserFilters "
strSQL = strSQL & " WHERE (([WhereClause]) = "
strSQL = strSQL & Chr(34) & fstrWhere & Chr(34) & ")"


Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)

i = rst.RecordCount

'default bln to FALSE
bln = False

If i > 0 Then
'set to TRUE
bln = True
FiltNm = rst("ShortDesc")
' Else
' bln = False
End If

rst.Close
End Function
'----------code beg --------------


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

I have a table which stores SQL statements as strings. I'm trying to filter
the table for a particular SQL string in VBA.

The code fails at: Set rst = db.OpenRecordset(strSQL). I get Run-time
error: 3075. The error message indicates that I'm missing an operator.

The code is below. The fstrWhere variable returned by the GetWhereClause
function equals:

"WHERE [UserID]= 'KZ1' AND ([ActualSaleDt] Between #8/1/2008# And #8/31/2008#)

Function CkFiltExists(bln As Boolean, FiltNm As String)

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim i As Integer
Dim strSQL As String
Dim fstrWhere As String

Call GetWhereClause(fstrWhere)

strSQL = "SELECT * From tbl_UserFilters WHERE (([WhereClause]) = '" &
fstrWhere & "')"

Set db = CurrentDb

Set rst = db.OpenRecordset(strSQL)

i = rst.RecordCount

If i > 0 Then
bln = True
FiltNm = rst("ShortDesc").Value
Else
bln = False
End If

rst.Close
End Function

In the immediate window, strSQL contains:

SELECT * From tbl_UserFilters WHERE (([WhereClause]) = 'WHERE [UserID]=
'KZ1' AND ([ActualSaleDt] Between #8/1/2008# And #8/31/2008#)'

Anyone know what I'm doing wrong.

Thanks,
Manuel