dd/mm/yyyy OZ vs US mm/dd/yyyy

[jenawyn]

I would like to know why this module which is called for calendar duty. My dates are in dd/mm/yyyy short date format in my tables but this module calls up data on a report for april whne there are no records in april but thinks 4/3/2002 is 3/4/2002. I there somewhere in this module I can cahnge something????? ANy help appreciated I have looked on the list and there are a lot of confused people out there with this problem


Module follows

Public Function dateStartRange(intRangeOption As Integer) As Date

Dim dateResult As Date

Select Case intRangeOption
Case 1
Let dateResult = #7/1/2002#
Case 2, 5
Let dateResult = DateSerial(Year(date), Month(date), 1)
Case 3, 6
Let dateResult = DateSerial(Year(date), Int((Month(date) - 1) / 3) * 3 + 1, 1)
Case 4, 7
Let dateResult = DateSerial(Year(date), 1, 1)
Case 8
Let dateResult = DateSerial(Year(date), Month(date) - 1, 1)
Case 9
Let dateResult = DateSerial(Year(date), Int((Month(date) - 1) / 3) * 3 + 1 - 3, 1)
Case 10
Let dateResult = DateSerial(Year(date) - 1, 1, 1)
Case 11
Let dateResult = DateSerial(Year(date) - 1, Month(date), Day(date) + 1)
End Select

dateStartRange = dateResult

End Function


Public Function dateEndRange(intRangeOption As Integer) As Date

Dim dateResult As Date
Dim strDate As Date

Select Case intRangeOption
Case 1
Let dateResult = #6/30/2010#
Case 2
Let dateResult = DateSerial(Year(date), Month(date) + 1, 0)
Case 3
Let dateResult = DateSerial(Year(date), Int((Month(date) - 1) / 3) * 3 + 4, 0)
Case 4
Let dateResult = DateSerial(Year(date), 12, 31)
Case 5, 6, 7, 11
Let dateResult = date
Case 8
Let dateResult = DateSerial(Year(date), Month(date), 0)
Case 9
Let dateResult = DateSerial(Year(date), Int((Month(date) - 1) / 3) * 3 + 4 - 3, 0)
Case 10
Let dateResult = DateSerial(Year(date) - 1, 12, 31)
End Select

dateEndRange = dateResult

End Function
-----------------------------------------
This is the Code for the OK Button on the form.

Private Sub btnOK_Click()
Dim strWhere As String
Dim strDocName As String
Dim strCtlStart As String
Dim strCtlEnd As String
Dim strMsg As String
Dim strhdr As String
'name of report put it here
'strDocName = strRptcivil_monthofficer
strDocName = "RPTcrim_sgtmonth"
strWhere = "[mydate1]" 'modify this to reference the date field

If Me.dateStart.Visible = True Then 'function supplied dates
strCtlStart = "dateStart"
strCtlEnd = "dateEnd"
Else 'user supplied dates
strCtlStart = "dateStartUser"
strCtlEnd = "dateEndUser"

If IsNull(Me.dateStartUser) Then
Let strMsg = "You must enter the start date for the range of " _
& "dates to be included in the report."
Let strhdr = "Invalid Start Date"
MsgBox strMsg, vbCritical + vbOKOnly, strhdr
Me.dateStartUser.SetFocus
Exit Sub
End If

If IsNull(Me.dateEndUser) Then
Let strMsg = "You must enter the end date for the range of " _
& "dates to be included in the report." & vbNewLine _
& vbNewLine & "If you want to include only one day, enter " _
& "the same date in both Date fields."
Let strhdr = "Invalid End Date"
MsgBox strMsg, vbCritical + vbOKOnly, strhdr
Me.dateEndUser.SetFocus
Exit Sub
End If

If Me.dateEndUser < Me.dateStartUser Then
Let strMsg = "The End Date of the date range must be equal to " _
& "or greater than the Start Date."
Let strhdr = "Invalid End Date"
MsgBox strMsg, vbCritical + vbOKOnly, strhdr
Me.dateEndUser.SetFocus
Exit Sub
End If
End If

strWhere = strWhere & " Between #" & Me(strCtlStart) & "# And #" _
& Me(strCtlEnd) & "#"

If strDocName = "" Then
MsgBox "There is no report specified for printing.@The date criteria " _
& "constructed is @" & strWhere, vbOKOnly + vbInformation
Else
DoCmd.OpenReport strDocName, acViewPreview, , strWhere
End If

End Sub

 

[Rich]

You have to use the US date format when using strings or try
& Format(Me!strCtlStart, "0")

 

[jenawyn]

Rich that format does not work any other suggestions

 

[Jack Cowley]

http://support.microsoft.com/default.aspx?scid=kb;en-us;q210069

 

[jenawyn]

I've tried that Jack but what calls the function, or is it automatic????

I could'nt get it to work. Oh well I'll keep on trying.... By reading all the posts on this subject no one seems to know the definite answer for this problem..I thought it would be just a simple change in the string code but looks like not.

 

[Jack Cowley]

Have you stepped through all of that code to see where Access chokes? Is it in the Where clause that you have? I suggest stepping through your code, noting the date variable and see when it goes South (Yank for 'bad'). At that point call the Function in the article and pass the current variable. The function will return a US date and now you can use that variable in the rest of your code. I hope that wasn't too esoteric...

 

[Rich]

It's something like
strSQL = strSQL & " WHERE [DeliveryDate] Between #" & Format(txtStDate, "mm/dd/yyyy") & "# And #" & Format(txtEnDate, "mm/dd/yyyy") & "#;"

[This message has been edited by Rich (edited 05-19-2002).]