Date Format problem in recordset

[hanski]

Hi

I have the following kind of program in my VBA:

Dim PUTKI As New ADODB.Connection
Dim tarj As New ADODB.Recordset

Dim ap As Date
Dim lp As Date

Set PUTKI = CurrentProject.Connection

ap = InputBox("Give first date")
lp = InputBox("Give last date")

tarj.Open "select * from database where GivenDate =" & ap, PUTKI,
adOpenDynamic, adLockOptimistic


When I start the recordet it gives me error: SYNTAX ERROR. ...
"GivenDate = 1.1.2006"

The value of ap is 1.1.2006 and the value of lp is 15.1.2006


Why does it not accept my date??

Hannu

 

[Rob Parker]

Date strings need to be delimited with the # character. Try:

tarj.Open "select * from database where GivenDate = #" & ap & "#", PUTKI,

HTH,

Rob

 

[hanski]

Thanks!!

Hannu

Rob Parker kirjoitti:

Date strings need to be delimited with the # character. Try:

tarj.Open "select * from database where GivenDate = #" & ap & "#", PUTKI,

HTH,

Rob

 

[RoyVidar]

hanski wrote in message

Hi

I have the following kind of program in my VBA:

Dim PUTKI As New ADODB.Connection
Dim tarj As New ADODB.Recordset

Dim ap As Date
Dim lp As Date

Set PUTKI = CurrentProject.Connection

ap = InputBox("Give first date")
lp = InputBox("Give last date")

tarj.Open "select * from database where GivenDate =" & ap, PUTKI,
adOpenDynamic, adLockOptimistic


When I start the recordet it gives me error: SYNTAX ERROR. ...
"GivenDate = 1.1.2006"

The value of ap is 1.1.2006 and the value of lp is 15.1.2006


Why does it not accept my date??

Hannu

I think you need to format the dates to US/ISO format. Check out
http://www.mvps.org/access/datetime/date0005.htm and
http://allenbrowne.com/ser-36.html

The last one, with some interesting explanations.

I use ISO 8601
....GivenDate =#" & format$(ap, "yyyy-mm-dd") & "#"

Since you are using inputboxes, you might need some additional testing
prior to using it in dynamic SQL, though.