Error in simple query

  • Thread starter Thread starter Arthur Erdös
  • Start date Start date
A

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
 
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??
Click to expand...

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)
 
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
 
Thx John, it was indeed the date format... ;)


John Vinson said:
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??
Click to expand...

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)
Click to expand...
 
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.
 
Back
Top