Solved Cannot Filter Date in Table (Data Type Mismatch) (1 Viewer)

jack555

Member
Local time
Tomorrow, 00:35
Joined
Apr 20, 2020
Messages
93
Having a field set as Date/Time and data recorded via Form using Now(); all works fine. When I open the table for some purpose and apply the date filter (yesterday, today last month alike), get the error message "Data Type Mismatch in criteria expression". However, when I run a query for the same field it accepts the date criteria without any problem and the same data when exported to excel works fine.

Not sure what causes the problem. and most of the forum posts address the issue in query criteria mismatch not in the table. Your expert advises, please.

1593922191604.png
1593922247187.png
1593922361078.png
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:35
Joined
May 7, 2009
Messages
19,169
check if there are "Blank" dates on that field and try "putting" some valid date there. otherwise, remove them.
 

jack555

Member
Local time
Tomorrow, 00:35
Joined
Apr 20, 2020
Messages
93
check if there are "Blank" dates on that field and try "putting" some valid date there. otherwise, remove them.

thanks for the response. Yes, there are blank fields since it will be until someone edits the record via the form. You are right, I tried deleting the blank records and date filter works. However, is there any other way than deleting to make this work? blank fields are required to be there.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:35
Joined
Feb 28, 2001
Messages
27,001
Your query issue is telling you those blanks CANNOT be there, never mind the requirement. But... there are ways to get around that.

The issue is that the BLANK field simply means you have no date. But suppose that you supplied a default value for those date fields that resolved to an impossible date? For instance, pick the Access "Epoch" date - 31 Dec 1899, which I think is day 0. Which means the date field, as a number, reads as zero. Anyone who was born on that date would be dead by now because (I believe) the world's oldest person is only 115? Anyway, the point is that if you leave that date AS A DATE - but just an impossible date - then what's the difference in testing for a blank vs. testing for a 0?

Before you say "displaying it" just remember that you can put tests in forms and queries so that they can intercept these "rogue" dates and show blanks when required. You can put IIF statements in place so that if you have the 0 date, you show one thing but if you have a "real" date you can show some other thing.

The key is looking at the purpose of those blanks and then finding something that does the same trick but in a way that doesn't make other parts of your code unhappy.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:35
Joined
May 7, 2009
Messages
19,169
Doc, it's Dec 30, 1899.

debug.print CDate(0) = #12/30/1899#

doc is write, maybe but some arbitrary value to Blank ones (0 or Dec 30, 1899).
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:35
Joined
Feb 28, 2001
Messages
27,001
Thanks, Arnel. I always get confused because among Access, Excel, and Windows itself, there are three Epoch dates, only two of which are the same and the third one is different, but I can never remember without looking it up which one is which. Then of course, there are different Epoch dates for UNIX, OpenVMS, and certain 3rd party big-name SQL engine DBs.
 

Isaac

Lifelong Learner
Local time
Today, 13:35
Joined
Mar 14, 2017
Messages
8,738
Thanks, Arnel. I always get confused because among Access, Excel, and Windows itself, there are three Epoch dates, only two of which are the same and the third one is different, but I can never remember without looking it up which one is which. Then of course, there are different Epoch dates for UNIX, OpenVMS, and certain 3rd party big-name SQL engine DBs.
I was once on a project on SQL server where the source data came from a system that used a LOT of UNIX timestamp ... the number of seconds since 1/1/1970 and placing them in bigint columns. Working with it wasn't hard when it came down to it, but mentally it just slowed me down a lot each time I had to digest a where clause or a join.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:35
Joined
Feb 19, 2002
Messages
42,975
Are you sure that Access is "seeing" this field as a date? Using the default SQL Server driver, Access only recognizes dates defined as datetime. Others come through as text which would explain the type mismatch.
 

jack555

Member
Local time
Tomorrow, 00:35
Joined
Apr 20, 2020
Messages
93
Your query issue is telling you those blanks CANNOT be there, never mind the requirement. But... there are ways to get around that.

The issue is that the BLANK field simply means you have no date. But suppose that you supplied a default value for those date fields that resolved to an impossible date? For instance, pick the Access "Epoch" date - 31 Dec 1899, which I think is day 0. Which means the date field, as a number, reads as zero. Anyone who was born on that date would be dead by now because (I believe) the world's oldest person is only 115? Anyway, the point is that if you leave that date AS A DATE - but just an impossible date - then what's the difference in testing for a blank vs. testing for a 0?

Before you say "displaying it" just remember that you can put tests in forms and queries so that they can intercept these "rogue" dates and show blanks when required. You can put IIF statements in place so that if you have the 0 date, you show one thing but if you have a "real" date you can show some other thing.

The key is looking at the purpose of those blanks and then finding something that does the same trick but in a way that doesn't make other parts of your code unhappy.

Thanks Doc, the blank date was the problem. managed to fill it up with dummy date.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:35
Joined
Feb 28, 2001
Messages
27,001
Yep, sometimes these dumb machines do best if you just give them what it seems they want. Glad that suggestion helped.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:35
Joined
Feb 28, 2001
Messages
27,001
Actually, Cronk, I used that in my Ancestry project along with the "end of epoch" date of 31-Dec-9999. One is an impossibly early date, the other is an impossible future date. The comparisons work just fine when looking for "unknown past date" or "unknown future date but I can even tell them apart and I never need to worry about "invalid use of null." Even though there are times when the Ancestry.COM database doesn't have a date to give me. When I do the GEDCOM file import from their download, any missing dates are set based on either "long time ago but damned if I know when" or "not yet happened and no prediction for the near future" by using one or the other of those dates.
 

Users who are viewing this thread

Top Bottom