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?