filter query on a calculated field

[bradmaunsell]

This question is about filtering (and sorting) a query on a field that is derived from a function.

Here's the issue:

* I have a table of insurance applications (tblApps)

* Some tblApps records are new biz and some are renewals

* New Biz apps have no previous policy that is expiring

* Notice these New Biz rows do not have a prevoius policy expiration date. For these, I substitute the "Date App Signed" (which is available in the tblApps record).

* Renewals have a previous policy that is due to expire.

* The renewals appliaction record does not contain the expiration date of the previous (expiring) policy

* I use a function "getPreviousExpirationDate(FID)" which returns the desired date. This works fine in the query of tblApps records.

All of the above works fine, but...

I want to place a "BegDate" and "EndDate" text box on the form header and cause the (continuous records) form to be filtered by >=BegDate and <=End Date. I need to to test against the date returned by my function (or new biz substitute date).

I have used this type of filter before and it works fine. However, I have always used it to filter a non-claculated field.

The problem above appears to be caused because the date returned by my function is a calculated result - that cannot be filtered (or sorted).

Any suggestions?

Thanks,
Brad
South Burlington, Vermont