Filter a report by a column which is auto-calculated from a different table

[Trial4life]

Hello,
I'm trying to filter a Report by a column of a Form which is auto calculated (using data of a different table) using a DLookUp expression (which reads the data from a different table), but the filter options are grayed out.





If I try to filter the report using another column which is not autocalculated (but refers to a column of the direct table which the Form is linked to), the filter menu turns on again.



If I want to filter the Report in this way, I have no options but to export the Report to an Excel sheet, and apply a filter to the desired column using the string criteria I need.

I was wondering if there is a way to "force" the filter directly in Access, using just the "RAW" data that comes out from the DLookUp expression and that are displayed on the Report view, so that I can directly export the Report to a PDF, saving a lot of time. The problem seems to be related to the fact that I'm using the DLookUp expression to calculate the column I want to filter, so the Form can't see the data because it is indeed calculated from a different table.

Thanks in advance.

[Micron]

A calculated control cannot be bound. If it can't be bound, it's not part of the underlying data (at least not in any fields), thus you cannot filter on it. If the parts of the calculation are fields in the underlying data, you could filter using the component parts as criteria. If they're not (e.g. you have an underlying query that provides the report data but only the calculated query field is passed to the report) then you'd have to split up the parts that are actually data in fields and apply a report filter using those. Far better to include the fields in the query even if you don't display them individually on the report, and easy enough to hide those fields.

Many will use a some sort of form with controls to provide criteria, and a combo with a list of reports. When you click the button, the report opens, filtered as required. Perhaps that is something that will make your job easier.

[Gicu]

Move the dLookup from the form itself (in the control's control source) to the form's record source.

Cheers,

[Trial4life]

I've tried to schematize in this image (simplifiyng as much as possible) what I'm trying to achieve:

Move the dLookup from the form itself (in the control's control source) to the form's record source.

Cheers,

I had already tried to move the DLookUp formula from the form to the record source (Table B), in the following way:



but when I try to save the Table B, I get this error window:

(«The expression cannot be used in a calculated column»)

[Gicu]

That was not what I suggested. If your form is bound to table "TABLE B" and you are looking up a matching value in table "TABLE A" open up your form in design view and in the record source property (in the Properties window) replace TABLE B with :

 SELECT * FROM [TABLE B], Dlookup("NAME","[TABLE A]","ID='" & [ID] & "'") As YourCalculatedNameField;

Once you do that go to the textbox on the form and in its control source replace the calculation with the calculated field (YourCalculatedNameField) from the query.

Cheers,

[Trial4life]

I've tried to do what you suggested, but as soon as I try to change the record source with the code you wrote, I get the error message: «Syntax error in FROM clause».
Also, note that the form is linked to Table B, but I'm displaying also other columns of the Table B itself, not only the column "Name" from Table A.

[Gicu]

Show please what you tried (the real names of the tables and fields), you select all tables from Table B (hence the "SELECT *") and you add your calculated dLookup field.

Cheers,

[Trial4life]

I managed to achieve what I was trying to do by just avoiding the DLookUp, and creating a new query from scratch. I created then a report directly from the new query, and now the filter works properly for all of the fileds in the report linked to the query.

Thanks for the help.