469,926 Members | 1,950 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,926 developers. It's quick & easy.

Combobox to filter table using a date

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.
Jul 29 '06 #1
2 5706
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)
Jul 31 '06 #2
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
Aug 1 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

6 posts views Thread by Doug Bell | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.