Advance query/report question

[Mike Cooper]

Hi everyone,

This is a tough one. I have a database full of solicitations,
identifying a customer and recording initial call, first followup,
second followup, etc.

My boss want to be able to generate a report showing a list of
customer who were call between two different dates that he types into
a form. That, I have done. My boss types in his two dates, the SQL
statement searches through the follow-up call fields for a date
between those two dates and prints that record out in the report when
it finds one.

Here's the tough part.

In a new column of the report, my boss wants the date of that call.

You see the difficulty. First I have to gather a set of data
containing dates within a dynamically specified range. Then from that
range I populate a new column with the particular date that satisfied
that test.

I though I was on the right track with the following obscenely bloated
IIF statement:

=IIf( not IsNull([Followup_4th]), [Followup_4th],=IIf( not
IsNull([Followup_3rd]), [Followup_3rd],=IIf( not
IsNull([Followup_2nd]), [Followup_2nd],=IIf( not
IsNull([Followup_1st]), [Followup_1st], [Initial_Call]))))

By making this statment the controlsource of a query field, I was able
to populate a column with the date of the LATEST contact made with
each customer. If there was a way that I could dynamically limit the
above IIF to a range of dates I would have my answer. But I can't
figure out how to change the control source of an individual query
field dyanamically, and I can't figure out out to successfully
introduce variables into an IIF statement.

Any Ideas?

[Salad]

Mike Cooper wrote:

Hi everyone,

This is a tough one. I have a database full of solicitations,
identifying a customer and recording initial call, first followup,
second followup, etc.

My boss want to be able to generate a report showing a list of
customer who were call between two different dates that he types into
a form. That, I have done. My boss types in his two dates, the SQL
statement searches through the follow-up call fields for a date
between those two dates and prints that record out in the report when
it finds one.

Here's the tough part.

In a new column of the report, my boss wants the date of that call.

You see the difficulty. First I have to gather a set of data
containing dates within a dynamically specified range. Then from that
range I populate a new column with the particular date that satisfied
that test.

I though I was on the right track with the following obscenely bloated
IIF statement:

=IIf( not IsNull([Followup_4th]), [Followup_4th],=IIf( not
IsNull([Followup_3rd]), [Followup_3rd],=IIf( not
IsNull([Followup_2nd]), [Followup_2nd],=IIf( not
IsNull([Followup_1st]), [Followup_1st], [Initial_Call]))))

By making this statment the controlsource of a query field, I was able
to populate a column with the date of the LATEST contact made with
each customer. If there was a way that I could dynamically limit the
above IIF to a range of dates I would have my answer. But I can't
figure out how to change the control source of an individual query
field dyanamically, and I can't figure out out to successfully
introduce variables into an IIF statement.

Any Ideas?

I would create a query that "groups by" between the two dates. Next,
I'd create a query that selects records between the two dates. And I
would then create a report/sub-report.

If you are using a flat file method...which it appears you are doing
from above...heck...write a function that passes your followup dates and
the date range and return a string of the dates that met the criteria.