X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> Is It Possible To Perform Search-filter On Calculated Fields Of A Continuousform, Access 2016    
post Mar 27 2020, 09:49 AM

Posts: 89
Joined: 19-February 20
From: Mumbai, India

For a Continuous Form (pls see the attached image) I am able to create combobox filters when the field is directly in the Form's RecordSource table. But I have a challenge in creating Filter ComboBox when the field is a calculated field. So my problem here is that the filter works but the filter ComboBox does not display correctly. Here are some details.

The form's RecordSource table is: InterviewList. The Fields are IntID, RRID, and some others as seen in the image.
There is another table called RecrReqn. The fields are RRID, Client Name, JobRole and some other fields.

Using a field RRID as connecting field I can get the Client name and the Job Role from the RecrReqn table. As seen in the image, I have used [cmbRRID].[Column](3) method to display the Client name and similarly JobRole.

Now to filter the Client, a simple method is to use RRID as the filter. But the Recruitment dept needs to display the Client name and the Job Role AND be able to filter using Client Name.

For the Filter Combobox (the first filter in the image) I used the Row Source query which is an Inner Join of two tables(the SQL is given below).
SELECT DISTINCT InterviewList.RRID, RecrReqn.Client, RecrReqn.JobRole
FROM InterviewList INNER JOIN RecrReqn ON (InterviewList.RRID = RecrReqn.RRID) AND (InterviewList.RRID = RecrReqn.RRID);

The result of this query when viewing in datasheet mode is fine - as required. The query results in a table having three fields. Hence, I have kept the properties of the ComboBox Filter as follows
ColumnCount = 3
Column Widths = 1cm;3cm;3cm
List Width = 7cm
Bound Column = 1

So I am expecting the drop down to display the three Columns:

RRID | Client Name | JobRole

However, it displays only RRID. The other two columns are blank. So although the filter works but the filter Combobox dropdown does not display the Client name and Job role. Any idea where I am going wrong?

Attached File(s)
Attached File  Search_on_Calculated_fields.jpg ( 200.93K )Number of downloads: 12
Go to the top of the page

Posts in this topic

Custom Search

RSSSearch   Top   Lo-Fi    5th July 2020 - 11:23 AM