help, SQL in MS Excel macro

[NYP]

Hi all,

can anyone help me with the following SQL statement pls? here's the code..

Dim strSQL As String

strSQL = "SELECT [StaffInformation].[Name], [AllLeave].[Start_Date], [AllLeave].[End_Date], [AllLeave].[LeaveType], [AllLeave].[CourseName] FROM AllLeave, StaffInformation WHERE [AllLeave].[StaffID] = [StaffInformation].[StaffID] And [AllLeave].[Start_Date] >= '" & Format(DTFrom.Value, "dd-mmm-yyyy") & "' And [AllLeave].[End_Date] <= '" & Format(DTTo.Value, "dd-mmm-yyyy") & "' ORDER BY AllLeave.Start_Date, AllLeave.End_Date, AllLeave.LeaveType"


With Rpt
If .State = adStateOpen Then
.Close
End If

.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockReadOnly

.Open strSQL, DB
MsgBox .RecordCount

End With


Both DTFrom & DTTo are DTPicker component
when i tried that i got error msg

Data type mismatch in criteria expression


can anyone tell me why?

Thanks

[MemorY]

hrmph i didnt find anything on Excel but manybe this might help you

Answer:

Issue

The SQL error 80040E07 occurs when attempting to use the "Insert Record" or the "Update Record" server behavior to set the value of a column in Microsoft Access with an empty string ("").


Reason

MS Access is characterized by strong data typing; it imposes a rigorous set of rules on given column values. The empty string value in the command text (SQL) cannot be stored in MS Access' "Date/Time" data type specified by the associated column.


Solution

Try to avoid inserting and/or updating columns of "Date/Time" data types in Access with empty strings, ("") or with any other value that does not correspond to the range of values specified for the data type.

http://www.xlinesoft.com/asprunner/d...expression.htm

[NYP]

still can't solve man.. anyway thanks for ur advice

[MemorY]

Try to add # to both sides of dates in your sql-query.
Like this:


strSQL = "SELECT [StaffInformation].[Name], [AllLeave].[Start_Date], [AllLeave].[End_Date], [AllLeave].[LeaveType], [AllLeave].[CourseName] FROM AllLeave, StaffInformation WHERE [AllLeave].[StaffID] = [StaffInformation].[StaffID] And [AllLeave].[Start_Date] >= #" & Format(DTFrom.Value, "dd-mmm-yyyy") & "# And [AllLeave].[End_Date] <= #" & Format(DTTo.Value, "dd-mmm-yyyy") & "# ORDER BY AllLeave.Start_Date, AllLeave.End_Date, AllLeave.LeaveType"

tx perttu