Anything wrong here[Resolved]

[Paradox]

Hallo guys
i'm producing reports from a table and i get a syntax error. the code is below. any ideas what cud be wrong?

Private Sub Command1_Click()
Dim rs As New ADODB.Recordset
 
    Opencon
    rsrequests.Close
    rsrequests.Open "SELECT * FROM Requests WHERE((DateReq) Between # " & DTPicker1.Value & "# AND #" & DTPicker2.Value & "#)ORDER BY Sort DESC", Cn, adOpenStatic, adLockReadOnly
    
    Set DataReport1.DataSource = rsrequests
    DataReport1.Show
    
    Set rsrequests = Nothing

[Dave Bleby]

Hello Paradox,
Ok, first thing that leaps out is (and I suspect that this is down to the way that code has been pasted in) that you have a dim statement on the same line as your Sub declaration.
Apart from that I don't think you need to have the "#" characters in the statement, if you are using strings you would need to qualify them with single quotes but if you are passing numbers they don't usually need qualifying.
Also I'm not sure about the brackets around "DateReq", these might need to be square brackets [].

Hope this helps,

Dave.

[erickwidya]

or try using [] bracket for Requests, sort

[Paradox]

Sorry for the paste, i changed it to the below code, no syntax error now but aint gettin the records. am i going wrong somewhere?

Thanks

Private Sub Command1_Click()
 
    Opencon
    rsrequests.Close
    rsrequests.Open "SELECT * FROM Requests WHERE((DateReq) Between  " & DTPicker1.Value & " AND " & DTPicker2.Value & ")", Cn, adOpenStatic, adLockReadOnly
    
    Set DataReport1.DataSource = rsrequests
    DataReport1.Show
    
    Set rsrequests = Nothing

[si_the_geek]

try this:

rsrequests.Open "SELECT * FROM Requests WHERE((DateReq) Between [b]'[/b]" & DTPicker1.Value & "[b]'[/b] AND [b]'[/b]" & DTPicker2.Value & "[b]'[/b])", Cn, adOpenStatic, adLockReadOnly

[Paradox]

Am now gettin this error message. what wrong

( The conversion of a char datatype to a datetime datatype resulted in an out of range datetime value).

What am i not doing. The Datereq field is a Datetime datatype.

[si_the_geek]

the database doesnt understand the value of DTPicker1.Value and DTPicker2.Value so change them into a different format, eg:


rsrequests.Open "SELECT * FROM Requests WHERE((DateReq) Between '" & Format(DTPicker1.Value, "dd mmm yyyy") & "' AND '" & Format(DTPicker2.Value, "dd mmm yyyy") & "')", Cn, adOpenStatic, adLockReadOnly

[Paradox]

Thanks, its resolved the err.