Combobox to filter table using a date

[The_Monkey]

I have created a form with a combo box to filter a subform using a date.

The filter works great but not with dates starting with a 0 e.g 01/07/2006

I have set the format to short date for the combo box, the table that feeds the combo box and the subform to be filtered.

I have also set the format in the code see below.

Private Sub Week_Ending_AfterUpdate()

[Form_Record Activity].Data_Subform.Form.FilterOn = False

If Week_Ending = 0 Then Exit Sub

Date_On_Box$ = Format$([Form_Record Activity].Week_Ending, "dd/mm/yyyy")



[Form_Record Activity].Data_Subform.Form.Filter = "[Week Ending Date] = #" & Date_On_Box$ & "#"


[Form_Record Activity].Data_Subform.Form.FilterOn = True

End Sub

I would be very grateful if anyone can help please.

[alzowze]

Hi The_Monkey

Try applying the same format to both fields in the filter criteria.

[Form_Record Activity].Data_Subform.Form.Filter = "Format([Week Ending Date].'dd-mmm-yyyy') = #" & Format(Date_On_Box$,'dd-mmm-yyyy') & "#"


Alan ";0)

[The_Monkey]

Many thanks for your help.

I did try that but did not work for some reason. I have been struggling with this for a week and then I tried the following code and it worked perfect.

'Turn Filter off
[Form_Record Activity].Rec_Act_Subform.Form.FilterOn = False
'Assign Combo box value to variable
Date = [Form_Record Activity].Week_Ending
'Set Form Filter to value of Combo Box variable
[Form_Record Activity].Rec_Act_Subform.Form.Filter = "((Rec_Act_Subform.[Week Ending] =" & "'" & Date & "'" & "))"
'Leave Filter on
[Form_Record Activity].Rec_Act_Subform.Form.FilterOn = True

Many thanks again for responding to my question

The Monkey :D