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
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