Solved

filtering a subform from a main form

Posted on 2011-03-24
3
219 Views
Last Modified: 2012-05-11
I have an access 2002 database with a filter/search form and subform, but when I try to filter it by date it just clears all of the entries without showing the filtered info. the other filters all work it is just the date one that does not work.

this is the line of code for the date filter.
Filter_Date = IIf(Forms.Equipment_Query_Form.Date_Search <> "", "Validated_Date = " & Forms.Equipment_Query_Form.Date_Search & " and ", "")

this is the code for one of the other filters that works OK
Filter_Model = IIf(Forms.Equipment_Query_Form.Model_Search <> "", "Model = '" & Forms.Equipment_Query_Form.Model_Search & "' and ", "")
Private Sub Run_Search()
Dim Equipment_Query_Subform_Form_Filter, Filter_Date, Filter_Review, Filter_Status, Filter_Equipment, Filter_Model, Filter_Condition
Equipment_Query_Subform.Form.Filter = ""

'DEFINE FILTERS
Filter_Date = IIf(Forms.Equipment_Query_Form.Date_Search <> "", "Validated_Date = " & Forms.Equipment_Query_Form.Date_Search & " and ", "")
Filter_Review = IIf(Forms.Equipment_Query_Form.Review_Search <> "", "Validation_Review_Date = " & Forms.Equipment_Query_Form.Review_Search & " and ", "")
Filter_Status = IIf(Forms.Equipment_Query_Form.Status_Search <> "", "Validation_Status = '" & Forms.Equipment_Query_Form.Status_Search & "' and ", "")
Filter_Equipment = IIf(Forms.Equipment_Query_Form.Equipment_Search <> "", "Equipment_Description = '" & Forms.Equipment_Query_Form.Equipment_Search & "' and ", "")
Filter_Model = IIf(Forms.Equipment_Query_Form.Model_Search <> "", "Model = '" & Forms.Equipment_Query_Form.Model_Search & "' and ", "")
Filter_Condition = IIf(Forms.Equipment_Query_Form.Condition_Search <> "", "Equipment_Condition = '" & Forms.Equipment_Query_Form.Condition_Search & "' and ", "")

Equipment_Query_Subform.Form.FilterOn = False
Equipment_Query_Subform_Form_Filter = Filter_Date + Filter_Review + Filter_Status + Filter_Equipment + Filter_Model + Filter_Condition

If Equipment_Query_Subform_Form_Filter <> "" Then Equipment_Query_Subform_Form_Filter = Mid(Equipment_Query_Subform_Form_Filter, 1, Len(Equipment_Query_Subform_Form_Filter) - 5) ' remove ' and'

Equipment_Query_Subform.Form.Filter = Equipment_Query_Subform_Form_Filter
If Equipment_Query_Subform_Form_Filter <> "" Then
        Me.Day_report_search_filter = Equipment_Query_Subform_Form_Filter
        Equipment_Query_Subform.Form.FilterOn = True
    Else: Equipment_Query_Subform.Form.FilterOn = False
    Me.Day_report_search_filter = "#"
End If

End Sub

Open in new window

0
Comment
Question by:Scubalad
  • 2
3 Comments
 
LVL 30

Accepted Solution

by:
hnasr earned 500 total points
ID: 35209800
Try:
Filter_Date = IIf(Forms.Equipment_Query_Form.Date_Search <> "", "Validated_Date = #" & Forms.Equipment_Query_Form.Date_Search & "# and ", "")
0
 

Author Comment

by:Scubalad
ID: 35213226
Hi hnasr, Thanks for the very prompt and helpful answer I have been looking at that line for so long and I kept missing the # signs.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 35213718
Welcome!
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question