Solved

filtering a subform from a main form

Posted on 2011-03-24
3
217 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
DCount with multiple criteria 3 23
Loop within Select Case 3 26
How to make an ADE file by code? 11 42
DCount using "OR" 4 19
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now