Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-14-2019, 03:22 AM   #1
Ksabai
Newly Registered User
 
Join Date: Jul 2017
Posts: 36
Thanks: 3
Thanked 0 Times in 0 Posts
Ksabai is on a distinguished road
Filter Form Data using Dates

Iam using the below code for filtering a data in a Subform, the filter works for dates in the current year. but if i include the from date starting 2018. it shows all the datas. kindly help.

If Len(txtFromDate) > 0 And Len(txtTodate) > 0 Then
If filterStr = "" Then
filterStr = filterStr & " ([ContDate] Between #" & txtFromDate & "# AND #" & txtTodate & "#)"

Else
filterStr = filterStr & " And ([ContDate] Between #" & txtFromDate & "# AND #" & txtTodate & "#)"
End If
ElseIf Len(txtFromDate) > 0 Then
If filterStr = "" Then
filterStr = filterStr & " ([ContDate] = #" & txtFromDate & "#)"
Else
filterStr = filterStr & " And ([ContDate] = #" & CDate(txtFromDate) & "#)"
End If
ElseIf Len(txtTodate) > 0 Then
If filterStr = "" Then
filterStr = filterStr & " ([ContDate] = #" & txtTodate & "#)"
Else
filterStr = filterStr & " And ([ContDate] = #" & CDate(txtTodate) & "#)"
End If
End If
Me.frmContList.Form.Filter = filterStr
If filterStr <> "" Then
Me.frmContList.Form.FilterOn = True
Else
Me.frmContList.Form.FilterOn = False
End If

Ksabai is offline   Reply With Quote
Old 01-14-2019, 03:41 AM   #2
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,107
Thanks: 0
Thanked 260 Times in 260 Posts
June7 will become famous soon enough
Re: Filter Form Data using Dates

So it works if date criteria are for 2019?

Are you using DD/MM/YYYY structure for date criteria?

Have you step debugged code?

Please use CODE tags around code to retain indentation and readability.
__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.
June7 is offline   Reply With Quote
Old 01-14-2019, 05:22 AM   #3
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 978
Thanks: 20
Thanked 250 Times in 243 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Filter Form Data using Dates

I would modify the code to wrap all the dates. If not in US regional settings you will have to.
Code:
Dim strFrom As String
Dim strTo As String

If IsDate(txtFromDate) And IsDate(txttoDate) Then
    strFrom = Format(Me.txtFromDate, "MM/DD/YYYY")
    strFrom = "#" & strFrom & "#"
    strTo = Format(Me.txttoDate, "MM/DD/YYYY")
    strTo = "#" & strTo & "#"
        If filterStr = "" Then
          filterStr = filterStr & " ([ContDate] Between " & strFrom & " AND " & strTo & ")"
        Else
          filterStr = filterStr & " And ([ContDate] Between " & strFrom & " AND " & strTo & ")"
        End If
    ElseIf Not IsDate(txttoDate) Then
       strFrom = Format(Me.txtFromDate, "MM/DD/YYYY")
       strFrom = "#" & strFrom & "#"

        If filterStr = "" Then
          filterStr = filterStr & " ([ContDate] = " & strFrom & ")"
        Else
          filterStr = filterStr & " And ([ContDate] = " & strFrom & ")"
        End If
    ElseIf Not IsDate(txtFromDate) Then
        strFrom = Format(Me.txttoDate, "MM/DD/YYYY")
        strFrom = "#" & strTo & "#"
        If filterStr = "" Then
          filterStr = filterStr & " ([ContDate] = " & strTo & ")"
        Else
          filterStr = filterStr & " And ([ContDate] = " & strTo & ")"
        End If
    End If
    Me.frmContList.Form.Filter = filterStr
    If filterStr <> "" Then
      Me.frmContList.Form.FilterOn = True
    Else
      Me.frmContList.Form.FilterOn = False
    End If
End If

MajP is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Use a form to filter dates on a query, or leave blank to show all dates ObiBenKenobi Queries 9 12-31-2014 10:28 AM
[SOLVED] Filter diary data using two dates in vba brainox Modules & VBA 7 06-07-2013 04:40 AM
Filter data between two dates within a form jleval Forms 14 11-08-2012 09:54 PM
Query to filter data with some dates are null msadiqrajani Queries 1 08-15-2012 08:27 PM
Filter sub form between two dates in text fields james_IT Modules & VBA 4 08-04-2012 09:08 AM




All times are GMT -8. The time now is 08:43 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World