Filter subform by nth column in combobox (1 Viewer)

Hello,

I am currently having trouble filtering my subform by a different column than the bound column set in properties.

the comobobox shows the ID for the last email sent, with the combobox drop down showing the name and date of email when dropped down. I have tried 2 things and neither work...

1) Calling the filter on the combo-box column itself:


I have used similar code on another form, but I can't get the myDate variable to get to value of the 6th column in the dropdown (Date Of Email). an Easier way to look at this would be:


2) Inside the subform, I have set up a seperate field that reads the result of the dropdown box column(5) and shows it. For example the Email with ID 22 has a date of 4/8/15, so any record with last sent email being 22 has a record that says 4/8/15. I am trying to use this value to filter between, but am unable of passing the value to my filter.

The textbox is called "Email Date" and the Control source is "=[Forms]![BenSearchForm]![BenSearchSub]![LastEmail].[Column](5)"

It shows the correct data, but does not allow me to filter by this field, when the apply filter button is pressed it asks me for the parameter value of the LastEmail Field.



I use a number of set variables for my filters, so my filter ends up looking like


where all but IDFilt begin with AND. I use this on a number of other forms so I am sure this is not the issue!

When I need to filter on column(5) of a combo, I use an invisible field.
When the user picks an item in the combo, in the afterupdate event, I update the invisible text box with txtBox = cboBox.column(5)
THEN the query can use the txtBox in the criteria.
Queries cannot see cboBox.column(5) (or any column except the bound one)

use this method to get extra criteria from the combo.

I tried to do this, but my filter options reside on my main from, and I am trying to filter a subform in datasheet view. I added a "dummy" field that only showed the column I needed [column], but I am unable to sort or filter by this date.

The combobox is on one of the fields inside the dataview subform, the values come from another table in the database.

Ok, so the answer to this was much easier than I first imagined. I used the same row source property for the drop down box that decides the filter, and the combobox inside the subform that is going to be filtered. Because of this I could filter without having to set a variable.

If anyone needs the code ask and I will post!