help, SQL in MS Excel macro
Results 1 to 4 of 4

Thread: help, SQL in MS Excel macro

  1. #1
    Member
    Join Date
    Nov 2001
    Posts
    37

    Question help, SQL in MS Excel macro

    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

  2. #2
    Banned
    Join Date
    Apr 2003
    Posts
    3,840
    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

  3. #3
    Member
    Join Date
    Nov 2001
    Posts
    37
    still can't solve man.. anyway thanks for ur advice

  4. #4
    Banned
    Join Date
    Apr 2003
    Posts
    3,840
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •