Weird SQL problem with date searching

rbuser

Hi,

Am having a strange problem with RB11 on Delphi2010 when using an
Access database.

I just drop a single table or query onto the Data page, then add a
date field to search on, set the operator to Between and click the
AutoSearch setting. I then run the report and, when prompted, enter
any two dates (or just leave it at today), then I get this error:

Syntax error in date in query expression '( Table1.CreatedDate BETWEEN
#2010-11-29 00::00::00# AND #2010-11-29 23::59::00# )'

Now the above SQL doesn't look correct for MS Access databases which
I'm assuming is what the error is all about. The question is, why is
it producing incorrect SQL like this?

Anyone have any ideas? I'm at a total loss.

Thanks,
Toby.

nicocizik

Hi Toby,

Take a look at the following article on how dates are formatted in DADE.
Also be sure you have your database type set to MS Access in the Database
settings.

http://www.digital-metaphors.com/rbWiki/DADE/Fundamentals/Date_Formatting


Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Hi Nico,

THanks for that. The format does look correct for Access according to
that so I'm not sure what the problem is.

I've just done some playing around with RB10 on Delphi 6 (we're
porting from this platform to RB11 on Delphi2010) at present.

The formatting of the dates looks identical on the old platform but
doesn't produce the error I get on the new one so now I'm really
baffled.

I'm setting the database type correctly - the problem only occurs on
Access databases - MSSQL server is fine.

Any help appreciated as I'm totally stuck and this is a rather large
problem atm, as you can probably imagine.

Cheers,
Toby.

rbuser

As an additional note, I've just tried the formatting with the "double
colon" in the time format in Access itself and it fails with the same
error. Removing the double colons and replacing with a single one
works fine.

Why is this formatting the default if Access doesn't like it? How
does this ever work? I'm confused.


On Tue, 30 Nov 2010 07:39:31 -0700, "Nico Cizik \(Digital Metaphors\)"

nardmoseley

There is now a patch available for RB 12.03 that resolves this issue.

The code has existed and worked many years. With RB 12.03 and D2007 the
existing code still works, so apparently something in Delphi has changed -
perhaps the Unicode VCL or some of the TDataSet internals or perhaps the
driver when used with Unicode behaves differently.

--
Nard Moseley
Digital Metaphors
www.digital-metaphors.com