Problem with DATES

[entex2]

Hi,

I'm trying to create a search form in MS Access 2000 to search for documents from a documents' register by their creation date. But I just can't get it working. The code in visual basic looks like this:

Private Sub runquery_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strStart As Date
Dim strSQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("testquery")

'strStart gets a value from a text box
strStart = Me.start.Value

strSQL = "SELECT PL.* FROM PL WHERE PL.docdate = #" & strStart & "#;"
qdf.SQL = strSQL
DoCmd.OpenQuery "testquery"
Set qdf = Nothing
Set db = Nothing

End Sub

On my PC and in Access I use date formation dd.mm.yyyy

When I enter a date into the text box and click OK button I get following error:
Syntax error in date in query expression 'PL.docdate=#03.02.2006#'

When I simplified the code it worked:

Private Sub runquery_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("testquery")

strSQL = "SELECT PL.* FROM PL WHERE (PL.docdate = [Forms]![test]![start]);"
qdf.SQL = strSQL
DoCmd.OpenQuery "testquery"
Set qdf = Nothing
Set db = Nothing

End Sub

But I want to use the strStart operator in SQL statment. By the way I also tried using the MS Date and Time Picker and Calendar Control, but without luck, I still got the same error message.

Can anyone help with this issue, I would be really grateful, I've been messing with it for several days.

Erkki

 

[FoFa]

Sounds like it doesn't like your date format, if you print both strSQL string, what is the differance?

 

[Karma]

Could it be that you have left the brackets out of the first SQL statement

strSQL = "SELECT PL.* FROM PL WHERE PL.docdate = #" & strStart & "#;"

vs

strSQL = "SELECT PL.* FROM PL WHERE [B]([/B]PL.docdate = [Forms]![test]![start][B])[/B];"

HTH

K.