Good afternoon,
I have been struggling with one of my reports. I collect data from NAV for this report. The Jet needs to filter on date but the field that I can use to filter on is a datetime format.
My setup should be that the filter should be for last week. So that would mean Today() untill Today()-7. The problem is that I don't seem to get this into a datetime format in my filter for Jet. Could anyone point me in the right direction?
Thanks
Filtering on datetimes are tricky. If you just use a date like 03/23/23 to filter on a field that is a datetime, you really are saying 03/23/23 at midnight but no other time. If you use a range, like 03/23/23..03/25/23, you are saying from the stroke of midnight 03/23 to the stroke of midnight of 03/25. You end up missing everything on 03/25 unless it happened right at midnight. If you need a range, you need to be a little creative.
Ideally, to make it easy, you should provide a start date and end date as filters. Then the filter for the period should be something like ">=[StartDate]&<[EndDate]+1". That will take you from the stroke of midnight on the Start Date but end right before the stroke of midnight the day after the EndDate.
My other advice is avoid using Today() functions. They are a volatile Excel function that will kill your run-times since it causes Excel to recalculate every time a cell is updated. I've seen this increase runtimes by 100-1000%.
Volatile Excel functions: https://learn.microsoft.com/en-us/office/client-developer/excel/excel-recalculation
