Evaluate filter before it's passed to DLookup function

[geebee]

Hi,

I have a criteria form with a multiselect [programvalue]
listbox used to build a query. Then I have a button which
opens a report based upon the query determined by values
slected in [programvalue] listbox. Here is the code
behind the button:

DoCmd.OpenForm "graphs for Monthly Travel Report
Summary", acNormal, "", "", , acHidden
'DoCmd.OpenReport "Allocation/Obligation/Expenditures
Summary", acPreview, "", ""
Dim varselected As Variant
Dim strSQL As String
For Each varselected In Me.programvalue.ItemsSelected
strSQL = strSQL & "'" & Me.programvalue.ItemData
(varselected) & "',"
Next varselected
If strSQL <> "" Then
strSQL = "[Program] IN (" & Left(strSQL, Len
(strSQL) - 0) & ")"

DoCmd.OpenReport "Allocation/Obligation/Expenditures
Summary", acPreview, , strSQL
Me.Visible = False
'test:
strSQL = ""
'end test

End If

The report opened contains a DLookup field. Now the
DLookup field has the following:

=DSum("[Allocation]","Program")

I would like to evaluate the strSQL so that a sum for
only programs selected in [programvalue] are reflected.

How can I do this?

I tried the following, but to no avail:

=DSum("[Allocation]","Program","strSQL")


Thanks in advance
geebee

 

[Pieter Wijnen]

in access 97 + you can query the .filter property (more programming..)
anyways DLookup is very slow, use your "own" version
Have a look at www.mvps.org/access (thanks Dev) for details

HTH

Pieter