By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 2,646 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

subform filtere between two dates (TextBoxes) and other comboboxes

P: 1
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 ::

Expand|Select|Wrap|Line Numbers
  1. 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") & " # "
  2. Me.Data_Capturing_Sheet_subform.Form.Filter = strFilter
  3. Me.Data_Capturing_Sheet_subform.Form.FilterOn = True
---------------

the error is somewhere after AND [HO_Name] please am desperate
Dec 10 '11 #1
Share this Question
Share on Google+
3 Replies


Expert Mod 2.5K+
P: 2,545
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.

Expand|Select|Wrap|Line Numbers
  1. strFilter = _
  2.    "[Client_first_name] Like '" & Me![Text39] & _
  3.    "*' AND [Client_Nature] Like '" & Me![Combo58] & _
  4.    "*' AND [BO_Advisor_Name] Like '" & Me![Text35] & _
  5.    "*' AND [Service_Group] Like '" & Me![Text54] & _
  6.    "*' AND [Atom_Name] Like '" & Me![Combo56] & _
  7.    "*' AND [Client_Last_Name] Like '" & Me![Text41] & _
  8.    "*' AND [Service_Details] Like '" & Me![Text31] & _
  9.    "*' AND [Client_Id] Like '" & Me![Text37] & _
  10.    "*' AND [HO_Name] Like '" & Me![Text33] & _
  11.    "*' AND [date_today] between # " & _
  12.    Format([Forms]![Generation_Report]![Text25], "DD/MM/YYYY") & _
  13.    " # AND # " & _
  14.    Format([Forms]![Generation_Report]![Text49], "DD/MM/YYYY") & _
  15.    " # "
  16. Me.Data_Capturing_Sheet_subform.Form.Filter = strFilter
  17. Me.Data_Capturing_Sheet_subform.Form.FilterOn = True
  18.  
-Stewart
Dec 10 '11 #2

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
Dec 10 '11 #3

NeoPa
Expert Mod 15k+
P: 31,273
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.
Dec 11 '11 #4

Post your reply

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