filter by form on a calculated control

[jcf378]

hi all.

I have a form which contains a calculated control ("days") that outputs the # of days between two dates (DateDiff command between the fields [date-of-birth] and [date-of-surgery]).

However, when I click "Filter by Form" in order to search for records based on this form, I would like to be able to enter a value in this "days" control so that I can filter records on the form based on this calculated interval (i.e., i want to search for records specifically where the # of days between date-of-birth and date-of-surgery is =5, or is <365, etc.). However, when i click "filter-by-form", the calculated control grays-out and does not permit entry of critera on which to filter.

Now, i can do this manually by designing a specific query where I enter the DateDiff command as an expression and by providing the desired criteria for the expression. However, I am trying to set the database up for "ease-of-use" with others, and would prefer not to require any users to make custom-queries. The only way I've found around this thus far is to actually create a new field in my Table called [date-interval], export my [date-of-birth] and [date-of-surgery] fields to Excel, calculate the date interval in Excel, and paste those values into the new [date-interval] field in my Access table; I can then place a control linked to the [date-interval] field in my form, and when I filter by form, I can achieve the desired effect of filtering by the [date-interval]. I am fine with having this added field in my table, but (1) I don't know how to make it a "calculated field" such that whenever a new record is added, the [date-interval] field is automatically populated with the proper value based on [date-of-birth] and [date-of-surgery]; and (2) access experts seem to recommend against having calculated fields in tables (again, I wouldn't mind doing it, but I don't know how to in this case).

So, to summarize, I am looking for a way to have a calculated control on a Form, that I could manually enter a filter criteria into when I toggle on the Filter-by-Form command, and if this is not possible, then I need a way to have a calculated field in my table that automatically fills itself in based on data from 2 other fields in that same table (and therefore I could filter-by-form based on this "calculated field").

sorry for the long post, but I want to be as descriptive as possible.
thanks in advance,
Jason

[nico5038]

Why not put the calculation in the form's query ?

Nic;o)

[NeoPa]

I surmise, from your description, that the Filter By option only works on the underlying recordset. It can't use fields simply displayed on the form (This makes sense if you think about it). Nico's solution is therefore the perfect solution for this situation :)