Thanks, Greg. Real good information. However, I think it only works within Access. But when making a SQL call via ODBC to the MDB database, it doesn't seem to work. I tried
Neither of these worked. But keep the suggestions coming.
I succeeded with
but on Windows, and the date format on my PC is yyyy-mm-dd.
I tried this to no avail:
The import comes through if I do '#01-01-2016#' using single quotes, but it just does a text qualifier and basically brings everything in and not just 2016. And just to check things out I tried #2016-01-01#, but that didn't work and that is not my computer format.
So it works on Windows. But that means you are probably using a Microsoft ODBC driver for Access. Since I'm on a Mac, I'm having to use the Actual Technologies ODBC driver for Mac. I guess it must be a limitation in the Actual Technologies driver. Bummer, but thanks for confirming it works on a Windows computer with FileMaker.
So another chance is on standard ODBC format as
Thanks for the idea, but no joy here. I tried
The last two actually tried to import but said no records found, but I know there are a couple thousand records created in 2016.
Good have another suggestion though. I appreciate it.
If the date literal syntax isn’t working, you could try the CDATE function. Something like:
WHERE "Invoice"."invoicedate" >= CDATE('2016-01-01 00:00:00')
The only other thing that comes to mind is to double-check the data type. Are you sure it’s a Date/Time and not another type of field?
The CDATE function didn't work. I did double check the data type to be date, but to confirm, I tried on another date field and it didn't work either with CDATE function or #2016-0101#. But good suggestion, Greg, because that would have certainly explained it.
I wonder if anyone from Actual Technologies is here that might comment since it is their drive.
For me Date selection works on Windows and Mac.
On Windows (Microsoft Access Driver) I use :
select * from YourTable where YourDate < #2016/09/27#
On Mac (ActualAcess Driver) I use:
select * from YourTable where YourDate < "2016-09-27"
CDate can not work. This is a Visual Basic Function. On Windows it is possible to use VB-Functions as SQL Functions in Access, with ActualAcess Driver onMac possibly not.
There must be some issue with the data. Maybe I have some bad data in there corrupting the import. Regardless, I created another database and imported a date field from a different table in the same Access file using the '2016-01-01' and it worked. But thanks for testing to confirm it was working with the Actual drivers. That helped.