Results 1 to 5 of 5
  1. #1
    Jgreenfield is offline Novice
    Windows XP Access 2003
    Join Date
    Nov 2011
    Posts
    3

    How to filter dates using an apply filter code.


    Hi,

    I was wondering if anyone could help me with some coding for a report that I am running?? I need the report to filter by upto 3 different sets of criteria. I have setup a code to filter by 2 of the 3 and it works great, the only one that I am having problems with is "Date". Is it possible to use an apply filter code to a date field or do I have to use a query on the date field and use that as the record source for the report. Currently I am using a form to input all the criteria into comboboxes and then have a command button that applies the filter to the open report. Also I need the report to bring back all results if the comboboxes are empty. Any help would be appreciated.....

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What is the date criteria - a range or a specific date? Should be able to filter. Check out this tutorial http://www.datapigtechnologies.com/f...tomfilter.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Jgreenfield is offline Novice
    Windows XP Access 2003
    Join Date
    Nov 2011
    Posts
    3
    I am trying to use a specific date. I have an embedded calender in the form that the user selects the date from. As they fill out all the criteria they then select apply filter and it filters the report based on the criteria entered into the comboboxes. I can not access the page you provided at work June, so i will have to look at it later. Could it be the way the date is formatted?? The combobox date is formatted as mm/dd/yyyy. I was looking to see if anyone knew how to apply this to the string data that the other filter options use. Here is the code as it applies to the other 2 sets of criteria, i am looking to see how to add Date to this string...

    Private Sub cmdapply_filter_Click()
    Dim strfoamtype As String
    Dim strdefectcode As String
    Dim strFilter As String
    ' Check that the report is open
    If SysCmd(acSysCmdGetObjectState, acReport, "rptmainform") <> acObjStateOpen Then
    MsgBox "You must open the report first."
    Exit Sub
    End If
    ' Build criteria string for Foam Type field
    If IsNull(Me.cbofoamtype.Value) Then
    strfoamtype = "Like '*'"
    Else
    strfoamtype = "='" & Me.cbofoamtype.Value & "'"
    End If
    ' Build criteria string for Defect Code field
    If IsNull(Me.cbodefectcode.Value) Then
    strdefectcode = "Like '*'"
    Else
    strdefectcode = "='" & Me.cbodefectcode.Value & "'"
    End If
    ' Combine criteria strings into a WHERE clause for the filter
    strFilter = "[Foam Type] " & strfoamtype & " AND [Defect Code] " & strdefectcode
    ' Apply the filter and switch it on
    With Reports![rptmainform]
    .Filter = strFilter
    .FilterOn = True
    End With
    End Sub

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Try:

    If Not IsNull(Me.dateboxname) Then
    strFilter = strFilter & IIf(strFilter = "", " ", " AND ") & "[date field] = #" & Me.dateboxname & "#"
    End If

    It is structure I use. I do this for each of the possible criteria. Only need the one variable - strFilter. I don't need to bother with the Like operator for text criteria.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Jgreenfield is offline Novice
    Windows XP Access 2003
    Join Date
    Nov 2011
    Posts
    3
    That was what I needed thanks so much June

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Apply Filter 2 criteria not applying
    By ahightower in forum Forms
    Replies: 4
    Last Post: 07-28-2011, 03:31 PM
  2. Option button to apply filter
    By catguy in forum Programming
    Replies: 4
    Last Post: 06-23-2011, 12:10 PM
  3. Apply Filter based on unbound date boxes
    By anoob in forum Access
    Replies: 3
    Last Post: 01-21-2011, 05:26 PM
  4. Replies: 5
    Last Post: 10-06-2010, 07:28 PM
  5. Apply filter command
    By miziri in forum Forms
    Replies: 6
    Last Post: 01-21-2010, 02:22 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums