subform filtere between two dates (TextBoxes) and other comboboxes

[alidakdouk]

Dear all
am trying to filter a subform by using multiple comboboxes (which is workin gperfectly) but when i needed to add two additional filters (Date_From) and (Date_To) the system is giving me a syntax error the code is as follows ::

strFilter = "[Client_first_name] Like '" & Me![Text39] & "*'  AND [Client_Nature] Like '" & Me![Combo58] & "*'  AND [BO_Advisor_Name] Like '" & Me![Text35] & "*' AND [Service_Group] Like '" & Me![Text54] & "*' AND [Atom_Name] Like '" & Me![Combo56] & "*' AND [Client_Last_Name] Like '" & Me![Text41] & "*' AND [Service_Details] Like '" & Me![Text31] & "*' AND [Client_Id] Like '" & Me![Text37] & "*' AND [HO_Name] Like '" & Me![Text33] & "*' AND [date_today] between # " & Format([Forms]![Generation_Report]![Text25], "DD/MM/YYYY") & " # AND # " & Format([Forms]![Generation_Report]![Text49], "DD/MM/YYYY") & " # "
Me.Data_Capturing_Sheet_subform.Form.Filter = strFilter
Me.Data_Capturing_Sheet_subform.Form.FilterOn = True

---------------

the error is somewhere after AND [HO_Name] please am desperate

[Stewart Ross]

I have rewritten your statement in a clearer multi-line format below. I am not seeing any syntax errors. Try the multi-line version below and see which line causes the error.

strFilter = _
   "[Client_first_name] Like '" & Me![Text39] & _
   "*' AND [Client_Nature] Like '" & Me![Combo58] & _
   "*' AND [BO_Advisor_Name] Like '" & Me![Text35] & _
   "*' AND [Service_Group] Like '" & Me![Text54] & _
   "*' AND [Atom_Name] Like '" & Me![Combo56] & _
   "*' AND [Client_Last_Name] Like '" & Me![Text41] & _
   "*' AND [Service_Details] Like '" & Me![Text31] & _
   "*' AND [Client_Id] Like '" & Me![Text37] & _
   "*' AND [HO_Name] Like '" & Me![Text33] & _
   "*' AND [date_today] between # " & _
   Format([Forms]![Generation_Report]![Text25], "DD/MM/YYYY") & _
   " # AND # " & _
   Format([Forms]![Generation_Report]![Text49], "DD/MM/YYYY") & _
   " # "
Me.Data_Capturing_Sheet_subform.Form.Filter = strFilter
Me.Data_Capturing_Sheet_subform.Form.FilterOn = True
 

-Stewart

[TheSmileyCoder]

I suggest you try looking at these two threads and see if any of it applies to your case.
literal datetimes and their delimiters
Discussion:SQL date literals - regional settings

I also most strongly urge you to always name your textboxes. It may seem trivial now, but it will save you alot of trouble down the road.

[NeoPa]

It never ceases to surprise me that memebers who claim to be desperate, still waste so much time and energy by posting poor quality, incomplete questions.

Actually, the advice already given is about all you could wish for. If you follow it all you will have made great strides and learned much. The one thing I would add is to include parentheses as a general rule to separate the different tests in your filter. Otherwise what is already here should help you resolve all the problems I see with your code and how you work with it.