Really odd filter behaviour (AC2007) (1 Viewer)

[AOB]

Hi guys,

Was investigating an issue this morning and came across some very odd behaviour when filtering a table.

The table has a number of date fields but concerned with one in particular (ImportDate)

When I open the table in datasheet view and filter on ImportDate (i.e. scroll down the list of filter options and tick "16-Sep-2015"), I only get one record. Thought this was odd as typically there would be north of a hundred records added each day.

If I clear the filter and reapply it by using Date Filters > Equals... > "ImportDate is equal to" and select today's date using the date picker, I get 81 records. Huh??...

If I clear the filter again, and filter on another date field (ValueDate) being equal to yesterday's date (typically, ImportDate lags ValueDate by one day), I get 111 records - all of which just happen to have an ImportDate of 16-Sep-2015? What the hell???

There are no other filters being applied in each scenario - so how many records do I have with an ImportDate of 16-Sep-2015? And why is the filter acting so inconsistently?

I then tried to get the subset using a very simple query :

SELECT tblIssues.*
FROM tblIssues
WHERE tblIssues.ImportDate=#9/16/2015#

This returns 81 records (so where are the other 30?)

I then tried :

SELECT tblIssues.*
FROM tblIssues
WHERE tblIssues.ValueDate=#9/15/2015#

This returns 115 records. 115?? So 4 more than when I simply filtered on the ValueDate field directly on the table in Datasheet view. Where were they in the filtered version?

Anybody have any idea what is going on here??

Thanks

AOB

 

[namliam]

Try

SELECT tblIssues.*
FROM tblIssues
WHERE tblIssues.ImportDate between #9/16/2015 00:00:00# and #9/16/2015 23:59:59#

My guess being you are not seeing the time component which makes a huge difference when looking for dates

 

[spikepl]

so you have records with a time element.

error 40!

I see namliam has seen this too

(whenever anyone complains of "odd behaviour" of Access, it's 99% certain it is an error 40)

 

[AOB]

Yessss...

Just beat me to it Mailman, I just discovered I was accidentally including the time portion when records were being manually added (it is pure date when they are automated) Have changed the update process now to exclude the time and will run a quick update query now to remove the existing times...

Thanks!

 

[AOB]

(whenever anyone complains of "odd behaviour" of Access, it's 99% certain it is an error 40)

What's error 40 spike, I'm not familiar with it?

 

[namliam]

I think error 40 is the reference to human error

 

[namliam]

btw, removing information like time in a date/time field in 90+% of all cases eventually comes back to haunt you

 

[AOB]

B******s, why is that Mailman??

 

[namliam]

because appearently the time will mean something or show something...
In this case i.e. the difference between automatic and manual process

Or even the different between morning and afternoon shifts...
Or why do we have this manual record at 3 am, who the F*c* is working at that time

Etc... etc...

 

[AOB]

Ah okay I getcha - I'm not overly concerned - the field isn't used for anything, I just have it so I can trace activity - I have separate fields to distinguish the other attributes that you've pointed out. Thanks for clarifying though, I was sweating for a second there...

 

[namliam]

90+%, considerable margin for exceptions Though it never hurts to point to the Obvious

I very rarely remove information from my tables... hide from users... plenty! or should I say a whole frigging lot! but throw away.... *ugh* so many times old information or information that "we are never going to need" has saved my behind

And so many times have I seen the loss of information hurt a process or people...