Results 1 to 6 of 6
  1. #1
    whisp0214 is offline Advanced Beginner
    Windows XP Access 2016
    Join Date
    May 2017
    Posts
    30

    Form not filtering dates correctly

    I have a form that lists all orders in the database. I am having issues with filtering based on the payment due dates. All of the orders and the due dates are pulled into the form via a query. That query has a calculated field "Date Due". The form has several filters on it, the the ones that aren't working are the date filters. If i enter 5/1/2018 into the start date and 5/31/2018 into the end date, my filter only filters based on the month and day. They completely ignore the year. As a result, my filtered query will have all May orders for all years listed. The only thing I can thing of is that the calculated field somehow is dropping the year and not formatting as a date. It looks correct in the query as it will have the proper year, but it just won't filter by it. Here is the calculated field. As you can see below, we have 3 payment methods. 1=monthy, 2 = quarterly, 3 = Bi-Annualy. The "Date Due" field is calculated based on how each customer pays.



    Code:
    Date Due: Switch([tblCommissionRates]![Pay Frequency]=1,DateAdd("d",[tblCommissionRates]![Payment Date]-Day([tblPurchaseOrders]![Due Date]),DateAdd("m",[tblCommissionRates]![Terms],[tblPurchaseOrders]![Due Date])),                                                          [tblCommissionRates]![Pay Frequency]=2,DateSerial(Year([tblPurchaseOrders]![Due Date]),(Int((Month([tblPurchaseOrders]![Due Date])-1)/3))*3+4,15),
                                        [tblCommissionRates]![Pay Frequency]=3,DateAdd("d",[tblCommissionRates]![Payment Date]-Day([tblPurchaseOrders]![Due Date]),DateAdd("m",13-Month([tblPurchaseOrders]![Due Date]),[tblPurchaseOrders]![Due Date])))
    The filter itself is pretty straight forward. I have two inputs, one for start date, one for end. If it is greater than the start date, it shows in the query. If it is less than the end date, it shows in the query.

    Code:
    If Nz(Me.txtBeginDate, "") <> "" Then    If Len(sFilter) > 0 Then sFilter = sFilter & " AND "
        sFilter = sFilter & " [Date Due]>= #" & Me.txtBeginDate & "#"
    End If
    
    
    If Nz(Me.txtEndDate, "") <> "" Then
        If Len(sFilter) > 0 Then sFilter = sFilter & " AND "
        sFilter = sFilter & " [Date Due]<= #" & Me.txtEndDate & "#"
    End If
    Does anyone have an idea why this won't filter correctly?

  2. #2
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Is Date Due being displayed on the form? Is it calculating correctly?

    June7 is correct in next post, If...Then wrong structure.
    Last edited by davegri; 04-24-2018 at 04:05 PM.

  3. #3
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    @davegri, actually, the inner If Then is one-liner version, no End If needed. Something else is issue.

    And your suggested correction has incorrect structure.
    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.

  4. #4
    whisp0214 is offline Advanced Beginner
    Windows XP Access 2016
    Join Date
    May 2017
    Posts
    30
    Date Due is being displayed and it is displaying correctly on a subform within the form.

  5. #5
    whisp0214 is offline Advanced Beginner
    Windows XP Access 2016
    Join Date
    May 2017
    Posts
    30
    I have another filter that operates on dates on the same form(when the order was received), and that works just fine. It is the exact same code as above except the field ID is referenced differently. Could it be a formatting issue within the field?

  6. #6
    whisp0214 is offline Advanced Beginner
    Windows XP Access 2016
    Join Date
    May 2017
    Posts
    30
    Issue is solved. It was a formatting issue with the calculated field. Access was reading the field as text instead of by date. I used CVDate() around the calculated field and that fixed it!

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

Similar Threads

  1. Replies: 8
    Last Post: 02-19-2016, 10:59 AM
  2. Printing out dates correctly
    By Lou_Reed in forum Access
    Replies: 1
    Last Post: 04-22-2015, 02:27 PM
  3. Replies: 15
    Last Post: 12-10-2012, 06:37 PM
  4. Filtering based on form dates
    By cbh35711 in forum Programming
    Replies: 3
    Last Post: 03-13-2012, 11:46 AM
  5. Filtering Report with between dates
    By patrickmcdiver in forum Reports
    Replies: 3
    Last Post: 02-22-2010, 12:11 PM

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