dynamic query help

G

geebee

hi all,

I have the following:

rst.Open "Select * from tbl_IDCF_Questions where" & sSQL, cnt

However, at runtime, I am getting an error message that says "Run-time error
'-2147217900 (80040e14)':
Syntax error in WHERE clause."

However, when the precedin code line is changed to the following, it works
just fine:
rst.Open "Select * From tbl_IDCF_Questions", cnt

how can I get it to where the sSQL works properly? I want users to be able
to construct SQL statements/filter the form dynamically.

for reference, here are te underling functions for the sSQL:
Private Function AttachAnd(sField, sValue)
If sValue = "''" Or sValue = "" Then
Exit Function
End If

If Occurancesnew(sSQL, "=") = 0 Then
sSQL = sSQL & sField & "=" & sValue
Else
sSQL = sSQL & " and " & sField & "=" & sValue
End If

End Function
Private Function BuildQueryCommand()



sSQL = ""

Call AttachAnd("index_number", "" & index_number_filter & "")
Call AttachAnd("control_number", "'" & control_number_filter & "'")
'Call AttachAnd("state", "'" & state_filter & "'")
'Call AttachAnd("activity", "'" & activity_filter & "'")
'Call AttachAnd("class", "'" & class_filter & "'")
'Call AttachAnd("program", "'" & program_filter & "'")
'Call AttachAnd("cost_center", "'" & cost_center_filter & "'")
'Call AttachAnd("FY02_BASETABLE.Start Date", "#" & test1 & "#")
'Call AttachAnd("end date", "'" & test2 & "'")


Filter = sSQL


FilterOn = True


'DoCmd.OpenReport "Phone", acViewPreview, , sSQL


End Function


Private Function Occurancesnew(sSQL, sOperator)
Dim offset
Dim iCount

offset = 1
While offset <> 0
offset = InStr(offset + 1, sSQL, sOperator)
If offset > 1 Then
iCount = iCount + 1
End If
Wend

Occurancesnew = iCount

End Function

Thanks in advance,
-geebee
 
M

[MVP] S.Clark

How about adding a space after the word WHERE, but before the closing double
quote?
 
T

Tom Ellison

Dear GB:

There is a simple technique I'd like to share. Declare a string variable.
I usually call it strSQL. Put the SQL in this variable before you use it:

DECLARE strSQL string

strSQL = "Select * from tbl_IDCF_Questions where" & sSQL

rst.Open strSQLs, cnt

Put a breakpoint on this last line. Observe what is in strSQL. You'll see
any simple errors you may have made. If you don't know what's wrong at this
point, post the SQL with your question in this NG.

Tom Ellison
 
G

geebee

Hi,

Tom, thanks for the StrSQL. It works just fine when there is a value in the
[control_number_filter] feld. But where there is not a value in the field, I
want ALL of the records to be downloaded. The following code is not working
for me:

If IsNull(Me.control_number_filter) Then
rst.Open "Select * From tbl_IDCF_Questions", cnt
Else
rst.Open strSQL, cnt
End If

Regardless of whether or not there i a value typed in the
[control_number_filter] field, ALL the records are being downloaded into the
spreadsheet. What can I do? I want only the filtered record(s) to be
downloaded if there is a value typed in the [control_number_filter] field,
and ALL the records to be downloadd if there is not a value typed in the
[control_number_filter] field.

Thanks in advance,
geebee
 
T

Tom Ellison

Dear GB:

Write a query that does what you want. Test it. Put that query in the
rstOpen function. Make sure it respects nulls. You could build the actual
SQL string in a variable, like strSQL I showed you, and check that it is
built properly.

Alternately, you could test cnt to see if it no value was entered. As a
result of this test, execute one of two different rstOpen statements, one
using cnt and the other using no parameter at all. I would still use strSQL
to build this string conditionally, there being two different versions of
the string based on that test.

The technique is quite simple. Write the SQL and get it working in all
cases. Test it. Then generate the SQL string in code and make sure it
looks correct in all cases. Test it. Then execute it in code in the
finished form. By not heaping up all the complexity of a finished form of
the code at once, but by approaching it one step at a time, with a few
seconds of testing of each case and a careful look at the results, you'll
have a working application in very little time. It's a matter of economics.
You can make 4 payments of $10 or a single payment of $1000. Which do you
prefer?

Tom Ellison


geebee said:
Hi,

Tom, thanks for the StrSQL. It works just fine when there is a value in
the
[control_number_filter] feld. But where there is not a value in the
field, I
want ALL of the records to be downloaded. The following code is not
working
for me:

If IsNull(Me.control_number_filter) Then
rst.Open "Select * From tbl_IDCF_Questions", cnt
Else
rst.Open strSQL, cnt
End If

Regardless of whether or not there i a value typed in the
[control_number_filter] field, ALL the records are being downloaded into
the
spreadsheet. What can I do? I want only the filtered record(s) to be
downloaded if there is a value typed in the [control_number_filter] field,
and ALL the records to be downloadd if there is not a value typed in the
[control_number_filter] field.

Thanks in advance,
geebee


geebee said:
hi all,

I have the following:

rst.Open "Select * from tbl_IDCF_Questions where" & sSQL, cnt

However, at runtime, I am getting an error message that says "Run-time
error
'-2147217900 (80040e14)':
Syntax error in WHERE clause."

However, when the precedin code line is changed to the following, it
works
just fine:
rst.Open "Select * From tbl_IDCF_Questions", cnt

how can I get it to where the sSQL works properly? I want users to be
able
to construct SQL statements/filter the form dynamically.

for reference, here are te underling functions for the sSQL:
Private Function AttachAnd(sField, sValue)
If sValue = "''" Or sValue = "" Then
Exit Function
End If

If Occurancesnew(sSQL, "=") = 0 Then
sSQL = sSQL & sField & "=" & sValue
Else
sSQL = sSQL & " and " & sField & "=" & sValue
End If

End Function
Private Function BuildQueryCommand()



sSQL = ""

Call AttachAnd("index_number", "" & index_number_filter & "")
Call AttachAnd("control_number", "'" & control_number_filter &
"'")
'Call AttachAnd("state", "'" & state_filter & "'")
'Call AttachAnd("activity", "'" & activity_filter & "'")
'Call AttachAnd("class", "'" & class_filter & "'")
'Call AttachAnd("program", "'" & program_filter & "'")
'Call AttachAnd("cost_center", "'" & cost_center_filter & "'")
'Call AttachAnd("FY02_BASETABLE.Start Date", "#" & test1 & "#")
'Call AttachAnd("end date", "'" & test2 & "'")


Filter = sSQL


FilterOn = True


'DoCmd.OpenReport "Phone", acViewPreview, , sSQL


End Function


Private Function Occurancesnew(sSQL, sOperator)
Dim offset
Dim iCount

offset = 1
While offset <> 0
offset = InStr(offset + 1, sSQL, sOperator)
If offset > 1 Then
iCount = iCount + 1
End If
Wend

Occurancesnew = iCount

End Function

Thanks in advance,
-geebee
 
Top