whts wrong with the sql statement [resolved]

[azrina]

i have a code from some source that display data from database to the flexgrid.

the prob is i want the data to be displayed depend on the selected date.
so i just put the sql statement which i think (may be the sql statement is wrong..) will display data according to the date.
and, an error ocurred whis is datatype mismatch and it points to the sql statement. why is that so??

Private Sub Command4_Click()
On Error GoTo errHandler
Set Conn = GetConnection
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM pemeriksaanBM WHERE tarikh = '" & DTPicker1.Value & "'", Conn, adOpenStatic, adLockOptimistic
'rs.Open "SELECT * FROM pemeriksaanBM ", Conn, adOpenStatic, adLockOptimistic
 
With rs
    If Not .EOF Then
      FG_ShowRecordset MSFlexGrid2, rs
    End If
    .Close
  End With
  Conn.Close
 
Set Conn = Nothing
  Set rs = Nothing
  Exit Sub
  
errHandler:
  MsgBox "An error occured." & vbLf & Err.Number & ": " & Err.Description, vbCritical
  Set Conn = Nothing
  Set rs = Nothing
  
End Sub

[BrianS]

Not sure if it's this line you mean, but see if this changes anything

rs.Open "SELECT * FROM pemeriksaanBM WHERE tarikh = " & DTPicker1.Value, Conn, adOpenStatic, adLockOptimistic

Or if you need the quotes around your DTPicker1.Value

rs.Open "SELECT * FROM pemeriksaanBM WHERE tarikh = " & """" & DTPicker1.Value & """", Conn, adOpenStatic, adLockOptimistic

[azrina]

are u sure with what u are saying ??
that makes syntax error..
do u have any good solution ?(besides that)

[randem]

azrina


Do this before posting sql statement, usually you can see whats wrong with the syntax:

SQL = "SELECT * FROM pemeriksaanBM WHERE tarikh = '" & DTPicker1.Value & "'"
Debug.Print SQL

What database are you using? This makes a difference in the date syntax.

ex.

Access - enclose date - # Date #

[azrina]

i use access 2003

[randem]

azrina


Use


"SELECT * FROM pemeriksaanBM WHERE tarikh = #" & DTPicker1.Value & "#"

of course tarikh should be an actual date field type.

[azrina]

thank u randem...it works

hope, u can help me again next time