Passing Dates between Query and Function 1

[gavinjb]

Hi, I am having a problem with a function I have written it all works fine until you pass a date to the function for the first of the month eg 01/05/2004, when the function does its work it converts the date to 05/01/2004, I have tried everything I can think of including using format function, but nothing seems to work, has anyone any ideas.

'---------------------------------------------------------------------------------------
' Procedure : TotalDocs
' DateTime  : 21/04/2004 16:03
' Author    : Blackford.G
' Purpose   : Returns Calc of Total Ammount of Docs in Database
'---------------------------------------------------------------------------------------
'
Public Function TotalDocs(dtStartDate As Date, dtEndDate As Date) As Integer
Dim strSQL As String
Dim rstADO As New ADODB.Recordset
    Debug.Print dtStartDate
    Debug.Print dtEndDate
    On Error GoTo TotalDocs_Error

    strSQL = "SELECT Count(Document.[Document ID]) AS MyCount FROM Service INNER JOIN Document ON Service.[Service ID] " & _
        "= Document.[Service ID] WHERE (((Service.Date)<=DateAdd(" & Chr$(34) & "d" & Chr$(34) & ",-28,Date()) AND " & _
        "Service.Date BETWEEN #" & dtStartDate & "# AND #" & dtEndDate & "#));"
    rstADO.Open strSQL, CurrentProject.Connection
    TotalDocs = rstADO!MyCount
    Debug.Print strSQL

TotalDocs_Exit:
   On Error GoTo 0
   Exit Function

TotalDocs_Error:
    Select Case Err.Number

        Case Else
            HandleError Err, "basSMReports.TotalDocs"
            Resume TotalDocs_Exit

    End Select
End Function

 

[RoyVidar]

When interacting with the Jet thru VBA sql strings, the dates needs to be in an US recognizable format. 01/05/2004 is January 5 to the Jet (problem exists for dates 1-12, 13+ Jet engine understands it can't be month). Try:

[tt]"Service.[Date] BETWEEN #" & format$(dtStartDate,"yyyy-mm-dd") & "# AND #" & format$(dtEndDate,"yyyy-mm-dd") & "#));"[/tt]

- you could also use the mm/dd/yyyy format - also - avoid using reserwed words as field names (Date is a function and datatype, which you can get around using [brackets])

Roy-Vidar

 

[gavinjb]

that worked perfectly, thanks for the help I had forgotten that you had to use US Dat format.