Hi,

Is it possible for a calculated control on a report to get it's
criteria from a pop-up dialogue form? My popup form tells the related
query to find any record with a first inspec. date, next date, last
date,etc. between the form's beginning and ending dates. The problem
is that I need to do a separate count of ALL the inspections falling
between the specified dates, and some records brought up have more
than one date that meet the criteria. So I wanted to put a calculated
control on the report to count each of the different inspection date
field records that fall between those dates, and then do a grand total
of all of the controls. Is this possible or do I have to find a
different way to do this?

So far, I have just been receiving the "#Error" message on my report,
and when I tried entering the expression in the actual query, I
received a message that the syntax was incorrect (missing operator) or
that the expression was too complicated.
Thanks to anyone who can help with this!

Hi Vanessa,

you could try storing the values of the pop-up dialogue form variables
locally inside the report.

set them as Public global variables at the start of the code, before
the procedures and functions.

e.g

Public startDate as Date

then when the report opens

public sub report_open()

startDate = [forms]![myForm]![startDate]

....

end sub

you'd have to make sure the dialog form is open though, but you can
test that first.

hope it helps,

David Thomas.

merryjane13 (AT) yahoo (DOT) com (Vanessa) wrote in message news:<7f5d3604.0408041016.461573ed (AT) posting (DOT) google.com>...

Hi Vanessa,

you could try storing the values of the pop-up dialogue form variables
locally inside the report.

set them as Public global variables at the start of the code, before
the procedures and functions.

e.g

Public startDate as Date

then when the report opens

public sub report_open()

startDate = [forms]![myForm]![startDate]

....

end sub

you'd have to make sure the dialog form is open though, but you can
test that first.

hope it helps,

David Thomas.

merryjane13 (AT) yahoo (DOT) com (Vanessa) wrote in message news:<7f5d3604.0408041016.461573ed (AT) posting (DOT) google.com>...

Vanessa,
Use a parameter query. For example:
PARAMETERS [Date From?] DateTime, [Date To?] DateTime;
SELECT INSP_TBL.*, [Date From?] AS DATE_FROM, [Date To?] AS DATE_TO
FROM INSP_TBL
WHERE INSP_TBL.INSP_DATE BETWEEN [Date From?] AND [Date To?]

This bit: ([Date From?] AS DATE_FROM, [Date To?] AS DATE_TO) uses the
ability of SQL to assign values to aliases to display the Date From and Date
To in their respective columns.

"Vanessa" <merryjane13 (AT) yahoo (DOT) com> wrote