Re: dynamic query help

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" <geebee@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4AF3047C-09AC-4A9A-80D1-547F2A28F59B@xxxxxxxxxxxxxxxx
> 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
>>


.