Error in simple query

[Arthur Erdös]

Hi NG,

I get a runtime error in following Syntax:

RS.Open Source:="SELECT v_ID FROM verwbuch WHERE mb_ID = 5 AND (dDatum
BETWEEN #01.01.2003# AND #31.12.2003)", ActiveConnection:="DSN=MyDsn",
CursorType:=adOpenKeyset

Does BETWEEN not work with Access?? Please help, any idea is appreciated!

thx in advance!

Arthur

 

[John Vinson]

Hi NG,

I get a runtime error in following Syntax:

RS.Open Source:="SELECT v_ID FROM verwbuch WHERE mb_ID = 5 AND (dDatum
BETWEEN #01.01.2003# AND #31.12.2003)", ActiveConnection:="DSN=MyDsn",
CursorType:=adOpenKeyset

Does BETWEEN not work with Access??

It does, but literal dates MUST be in US month/day/year format or an
unambiguous format. Try

BETWEEN #1/1/2003# AND #12/31/2003#

or

BETWEEN #1-Jan-2003# AND #31-Dec-2003#

If you want this query to always pull the records for the current year
without needing to edit the query every year, I'd suggest a different
technique:

.... AND Year([dDatum]) = Year(Date)

 

[Bryan Martin]

RS.Open Source:="SELECT v_ID FROM verwbuch WHERE mb_ID = 5 AND (dDatum
BETWEEN #01.01.2003# AND #31.12.2003#)", ActiveConnection:="DSN=MyDsn",
CursorType:=adOpenKeyset

 

[Arthur Erdös]

Thx John, it was indeed the date format...

Hi NG,

I get a runtime error in following Syntax:

RS.Open Source:="SELECT v_ID FROM verwbuch WHERE mb_ID = 5 AND (dDatum
BETWEEN #01.01.2003# AND #31.12.2003)", ActiveConnection:="DSN=MyDsn",
CursorType:=adOpenKeyset

Does BETWEEN not work with Access??

It does, but literal dates MUST be in US month/day/year format or an
unambiguous format. Try

BETWEEN #1/1/2003# AND #12/31/2003#

or

BETWEEN #1-Jan-2003# AND #31-Dec-2003#

If you want this query to always pull the records for the current year
without needing to edit the query every year, I'd suggest a different
technique:

... AND Year([dDatum]) = Year(Date)

 

[Pat Garard]

RS.Open Source:="SELECT v_ID FROM verwbuch WHERE mb_ID = 5 AND (dDatum
BETWEEN #01.01.2003# AND #31.12.2003)", ActiveConnection:="DSN=MyDsn",
^
CursorType:=adOpenKeyset MISSING '#'
--
Hope this helps!

Pat Garard
Australia
apgarardATbigpondDOTnetDOTau

"One look is worth a thousand rumours."
Wen Hou, Warring States Period.

"Look twice!"
Pat Garard, 2003.