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