A date problem with more than 10 years

[José António Silva]

There’s an Access issue that comes at least from Access 2000, related with
how to express dates in sql criteria expressions.

Access help always have been saying that date should be expressed like
#mm/dd/yy#. This is also coherent whit what we get from the design grid.
However, if we use an accde or compiled accdr file (equal with mde) this
date format always have raised an error. Actually, with Access 2007 I’m
getting Error 0 (zero), which Ҡdescription.

I’m using accdr compiled files with ODBC connections to SQL.
I’m also using Access run-time Portuguese version and computers with Windows
Portuguese versions. So, the regional date format is dd-mm-yyyy. I’m fairly
convinced that this error must have to do with this. BUT, WHAT ABOUT OTHER
LANGUAGES? For example, the UK format, which is dd/mm/yyyy.

The solution I’have ever used for this is to put in the SQL expression
datefield = CDate( “regional format†). Of course, in VBA we could walk
around ODBC and use OLEDB, where this don’t happen. But to change forms and
reports recordsource property we don’t have any other option.

Did anybody else have notice this problem? Does anybody know other solution?
Will this continue with Access 14?

This is not my first post about this. Several years ago, I asked the same
thing and I had no answers.

José António Silva

 

[José António Silva]

Hi Erez,
I’m glad to have found someone else with the same problem. Finally!

I appreciated the couple of solutions you have suggested. Thank you.

“My†solution differs from yours because I use WHERE StartDate >
CDate(“2009-01-01â€) whereas you use Where cLng(StartDate)>39814. If we use
this with SQL server, the main question here is: are your version forcing the
query to be filtered in Jet/ADE or is it possible that Jet/ADE send the
expression cLng(StartDate) to be evaluated by SQL Server. This later case
doesn’t seem obvious to me.

But, the other REALLY IMPORTANT THING about this is that:
1) We are here reporting a VERY OLD AND SERIOUS PROBLEM.
2) Jet has recently changed to ADE and the problem still remains.
3) We both have found workarounds, but what about if these workarounds
don’t work in the future.
4) Almost nobody seems to be aware of this.
5) How much time we still have to wait – outside of USA (because of
date format) – to run a query in a compiled accdb simply saying - I repeat -
simply saying StartDate > #1/1/2009#.

JS

 

[José António Silva]

Hi Erez,
I’m glad to have found someone else with the same problem. Finally!

I appreciated the couple of solutions you have suggested. Thank you.

“My†solution differs from yours because I use WHERE StartDate >
CDate(“2009-01-01â€) whereas you use Where cLng(StartDate)>39814. If we use
this with SQL server, the main question here is: are your version forcing the
query to be filtered in Jet/ADE or is it possible that Jet/ADE send the
expression cLng(StartDate) to be evaluated by SQL Server. This later case
doesn’t seem obvious to me.

But, the other REALLY IMPORTANT THING about this is that:
1) We are here reporting a VERY OLD AND SERIOUS PROBLEM.
2) Jet has recently changed to ADE and the problem still remains.
3) We both have found workarounds, but what about if these workarounds
don’t work in the future.
4) Almost nobody seems to be aware of this.
5) How much time we still have to wait – outside of USA (because of
date format) – to run a query in a compiled accdb simply saying - I repeat -
simply saying StartDate > #1/1/2009#.

JS

 

[John W. Vinson]

Hi Erez,
I’m glad to have found someone else with the same problem. Finally!

I appreciated the couple of solutions you have suggested. Thank you.

“My” solution differs from yours because I use WHERE StartDate >
CDate(“2009-01-01”) whereas you use Where cLng(StartDate)>39814. If we use
this with SQL server, the main question here is: are your version forcing the
query to be filtered in Jet/ADE or is it possible that Jet/ADE send the
expression cLng(StartDate) to be evaluated by SQL Server. This later case
doesn’t seem obvious to me.

But, the other REALLY IMPORTANT THING about this is that:
1) We are here reporting a VERY OLD AND SERIOUS PROBLEM.
2) Jet has recently changed to ADE and the problem still remains.
3) We both have found workarounds, but what about if these workarounds
don’t work in the future.
4) Almost nobody seems to be aware of this.
5) How much time we still have to wait – outside of USA (because of
date format) – to run a query in a compiled accdb simply saying - I repeat -
simply saying StartDate > #1/1/2009#.

JS

Have you tried using the (also acceptable) unambiguous ISO format yyyy-mm-dd?

 

[John W. Vinson]

Hi Erez,
I’m glad to have found someone else with the same problem. Finally!

I appreciated the couple of solutions you have suggested. Thank you.

“My” solution differs from yours because I use WHERE StartDate >
CDate(“2009-01-01”) whereas you use Where cLng(StartDate)>39814. If we use
this with SQL server, the main question here is: are your version forcing the
query to be filtered in Jet/ADE or is it possible that Jet/ADE send the
expression cLng(StartDate) to be evaluated by SQL Server. This later case
doesn’t seem obvious to me.

But, the other REALLY IMPORTANT THING about this is that:
1) We are here reporting a VERY OLD AND SERIOUS PROBLEM.
2) Jet has recently changed to ADE and the problem still remains.
3) We both have found workarounds, but what about if these workarounds
don’t work in the future.
4) Almost nobody seems to be aware of this.
5) How much time we still have to wait – outside of USA (because of
date format) – to run a query in a compiled accdb simply saying - I repeat -
simply saying StartDate > #1/1/2009#.

JS

Have you tried using the (also acceptable) unambiguous ISO format yyyy-mm-dd?

 

[José António Silva]

Hello John,

With the format yyyy-mm-dd I get the error 3146 “[Microsoft][ODBC SQL Server
Driver][SQL Server]Syntax error converting datetime from character string.
(#241).

However, I can add other detail to this discussion.

For example, up to Access 2003 the below expression works (1) when assigned
to a form recordsource and (2) when used to open a recordset. Both - the form
and the recordset – running against ODBC linked tables to a SQL Database.
This expression runs OK within a mde database – where I’m focused.

WHERE Table.StartDate >= #1/1/2009 0:0:0# AND Table.StartDate <= #1/31/2009
23:59:59#

Now, in Access 2007 accde or compiled accdr even this expression doesn’t
work anymore. I get the error 0 with Ҡdescription. But this expression
still works fine in a accdb.

So, changing from Access 2003 to Access 2007 we have losted a workaround.
Adding the time to a USA well formatted date doesn’t solve the problem
anymore.

This is one reason why I’m worried. Will we lose another workaround in
Access 14?
JS

 

[José António Silva]

Hello John,

With the format yyyy-mm-dd I get the error 3146 “[Microsoft][ODBC SQL Server
Driver][SQL Server]Syntax error converting datetime from character string.
(#241).

However, I can add other detail to this discussion.

For example, up to Access 2003 the below expression works (1) when assigned
to a form recordsource and (2) when used to open a recordset. Both - the form
and the recordset – running against ODBC linked tables to a SQL Database.
This expression runs OK within a mde database – where I’m focused.

WHERE Table.StartDate >= #1/1/2009 0:0:0# AND Table.StartDate <= #1/31/2009
23:59:59#

Now, in Access 2007 accde or compiled accdr even this expression doesn’t
work anymore. I get the error 0 with Ҡdescription. But this expression
still works fine in a accdb.

So, changing from Access 2003 to Access 2007 we have losted a workaround.
Adding the time to a USA well formatted date doesn’t solve the problem
anymore.

This is one reason why I’m worried. Will we lose another workaround in
Access 14?
JS

 

[José António Silva]

Hello Erez,
I’m very very surprised about what you have sad. You almost don’t use
datetime fields in a SQL server database because of this problem. Great!!

But this leads me again to the following relevant problem: are just the two
of us the only developers in the world that are not capable of running decent
queries with date fields?

This must be a too much serious problem to remain unsolved and to be
unknown. Hello world!!!! Is anybody else there????

JS

 

[José António Silva]

Hello Erez,
I’m very very surprised about what you have sad. You almost don’t use
datetime fields in a SQL server database because of this problem. Great!!

But this leads me again to the following relevant problem: are just the two
of us the only developers in the world that are not capable of running decent
queries with date fields?

This must be a too much serious problem to remain unsolved and to be
unknown. Hello world!!!! Is anybody else there????

JS