RE: dynamic query help

Tech-Archive recommends: Speed Up your PC by fixing your registry

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
spread***. 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" wrote:

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

Quantcast