Using date/time criteria in ADO SELECT statements

G

Gesualdo

I'm trying to use a date/time variable in an ADO SELECT statement, but I get
a data type mismatch error message relating to the time element. The variable
is in the VBA Date data type and is shown as (e.g.) "06/02/06 10:13:12".
Help! I'm trying to synchronise data between a SQL Server database and an
Access one.

The string is "SELECT * FROM [TblPeople] WHERE [LastModded]<'" & SynchPoint
& "';" Synchpoint is the date variable.
 
S

sjoo

DateTiime Type must be enclosed with #.
Please try the following;
SELECT * FROM [TblPeople] WHERE [LastModded]<#'" & SynchPoint
& "'#;"

Best regards,
sjoo
 
G

Gesualdo

Thanks for that Sjoo, but it doesn't work - I'm working in a VBA environment,
by the way. Using comparison operators for datetime seems to be a problem.
The code runs with just the "'" marks at either end, but fails to produce a
correct result; it crashes if I add #s as well, generating an "incorrect
syntax" error. Any further thoughts would be most gratefully received!

Gesualdo
 
C

Cindy M -WordMVP-

Hi =?Utf-8?B?R2VzdWFsZG8=?=,
I'm trying to use a date/time variable in an ADO SELECT statement, but I get
a data type mismatch error message relating to the time element. The variable
is in the VBA Date data type and is shown as (e.g.) "06/02/06 10:13:12".
Help! I'm trying to synchronise data between a SQL Server database and an
Access one.

The string is "SELECT * FROM [TblPeople] WHERE [LastModded]<'" & SynchPoint
& "';" Synchpoint is the date variable.
Click to expand...
I'm not sure, and you'd probably do better to ask in the data.ado newsgroup,
but...

I think SQL Server OLE DB interface requires dates to be in the format
yyyy-mm-dd.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail :)
 
G

Gesualdo

Thanks Cindy - that works! I am very grateful.

Gesualdo

Cindy M -WordMVP- said:
Hi =?Utf-8?B?R2VzdWFsZG8=?=,
I'm trying to use a date/time variable in an ADO SELECT statement, but I get
a data type mismatch error message relating to the time element. The variable
is in the VBA Date data type and is shown as (e.g.) "06/02/06 10:13:12".
Help! I'm trying to synchronise data between a SQL Server database and an
Access one.

The string is "SELECT * FROM [TblPeople] WHERE [LastModded]<'" & SynchPoint
& "';" Synchpoint is the date variable.
Click to expand...
I'm not sure, and you'd probably do better to ask in the data.ado newsgroup,
but...

I think SQL Server OLE DB interface requires dates to be in the format
yyyy-mm-dd.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)


This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail :)
Click to expand...
 
C

Cindy M -WordMVP-

Hi Gesualdo,
that works!
Click to expand...
Thanks for letting me know my memory isn't quite as bad as
I sometimes have the impression :) Glad you're up and
running!

-- Cindy Meister
 
Top