weird date filtering
Posted on 2012-12-25
hey guys, i've got these lines of code and the funny thing is that when my
txtServicingWorkHoursStartDate = "28 nov"
txtServicingWorkHoursEndDate= "30 nov"
it works fine.
but if i change my txtServicingWorkHoursEndDate to anywhere between 1 dec to 11 dec it doesn't work. meaning no records shown - even the ones from 28 to 30 nov which was showing properly previously. even if i use 01 dec or 1 dec 2012 or 01 dec 2012. this is quite strange. any possible explanations?
Forms!frmMTKManager.subfrmServicingWorkHours.Form.Filter = "[CalendarDate] >= #" & DateValue(Me.txtServicingWorkHoursStartDate) & "# AND [CalendarDate] <= #" & DateValue(Me.txtServicingWorkHoursEndDate) & "#"
Forms!frmMTKManager.subfrmServicingWorkHours.Form.FilterOn = True
Forms!frmMTKManager.subfrmServicingWorkHours.Form.AllowFilters = False
btw datevalue is giving me the correct dates - i've tested it with ?month(DateValue(Me.txtServicingWorkHoursEndDate) in the immediate window. thanks guys!!
update - it gives me the same problem for the start date. if i put the start date as anything before 1 dec to 11 dec it works fine.
start date 12 dec and end date 12 dec gives me records for 12 dec. when i change start date to 11 dec bham! i get records from 26 oct to 12 dec. so weird!!!! (compacting and repairing the database doesn't help as well!!)