Report DLookup Field Filtering

[mstery]

I have a report generated via an ID selection made in a dropdown on a
form. The report filters by an on click event in a preview report
button on the form. Everything in the report, including subreports,
filters perfectly, with the exception of a running sum DLookup field on
the main report. This field looks up a value in a foreign table. I'm
not clear where I'm supposed to be filtering this field, since it
obviously isn't picking up the filter generated by the form's on click
event. I've tried using:

=Dlookup("[Foreign Table Name]","[Foreign Table Field
Name]","[ID]=[Forms]![Name of Form]![ID]"), as well as created an On
Open event in the report that filters based on the ID selected in the
form. Neither works--everything generates an error message in the
DLookup field. Help??? Thanks.

[Bruno Campanini]

<ms****@soon.com> wrote in message
news:11********************@f14g2000cwb.googlegrou ps.com...

I have a report generated via an ID selection made in a dropdown on a
form. The report filters by an on click event in a preview report
button on the form. Everything in the report, including subreports,
filters perfectly, with the exception of a running sum DLookup field on
the main report. This field looks up a value in a foreign table. I'm
not clear where I'm supposed to be filtering this field, since it
obviously isn't picking up the filter generated by the form's on click
event. I've tried using:

=Dlookup("[Foreign Table Name]","[Foreign Table Field
Name]","[ID]=[Forms]![Name of Form]![ID]"), as well as created an On
Open event in the report that filters based on the ID selected in the
form. Neither works--everything generates an error message in the
DLookup field. Help??? Thanks.

Supposing [ID] is numeric:
=Dlookup("[Foreign Table Name]","[Foreign Table Field
Name]","[ID]=" & [Forms]![Name of Form]![ID])

If [ID] is string:
=Dlookup("[Foreign Table Name]","[Foreign Table Field
Name]","[ID]='" & [Forms]![Name of Form]![ID] & "'")

Bruno