Bloody annoying date problem - used as part of large filter for report (1 Viewer)

[Jibbadiah]

Hey folks, I have worked on this for a bit... and have finally given up. The filter works for the other variables, but the dates don't work. My regional settings are set to UK, but I think it may be converting them to US format in Access. The filter on dates either applies and brings back random results, or it brings back nothing. I have tried many variations. Maybe what I want is to do a comparison based on the date integer - can someone give me their best shot please... may be a good tester for Pat or SJ!!

Private Sub Populate_Historic_Report_Click()
Dim Start_Date As Date
Dim End_Date As Date
Dim StrSortcode As String
Dim StrDate_Selection As String
Dim StrProspects As String
Dim FullString As String

If IsNull(Me!Start_Param) Then
Start_Date = CDate(Format("26/05/2005", "dd/mm/yyyy"))
Else: Start_Date = CDate(Format(Me!Start_Param, "dd/mm/yyyy"))
End If

If IsNull(Me!End_Param) Then
End_Date = CDate(Format(Now(), "dd/mm/yyyy"))
Else: End_Date = CDate(Format(Me!End_Param, "dd/mm/yyyy"))
End If

StrDate_Selection = "(((Run_Date) Between (#" & Start_Date & "#) And (#" & End_Date & "#)) AND "

If IsNull(Me!Sortcode_Param) Then
StrSortcode = "((Best_Branch) Is Not Null) AND "
Else: StrSortcode = "((Best_Branch) = '" & Me!Sortcode_Param & "') AND "
End If

If IsNull(Me!Prospect_Param) Then
StrProspects = "((Keycode) Is Not Null))"
Else: StrProspects = "((Keycode) = '" & Me!Prospect_Param & "'))"
End If

FullString = "" & StrDate_Selection & StrSortcode & StrProspects & ""

DoCmd.OpenReport "Prospects_Historic_Report", acViewPreview, , FullString

End Sub

 

[namliam]

CDate expects MM/DD/YYY format not dd/mm
Why use CDate(format()) anyway if the fields are allready dates?
Why use format(NOW) if you can use Date() ?
Why use Me! instead of Me. ?

Greetz

 

[Jibbadiah]

You might ask that... I got to the crappy code, because my "good" code didn't work either... my good code is obviously crap - but it works sometimes

So... on with your suggestions... this doesn't work either.

If IsNull(Me.Start_Param) Then
Start_Date = ("26/05/2005")
Else: Start_Date = Me.Start_Param
End If

If IsNull(Me.End_Param) Then
End_Date = Date
Else: End_Date = Me.End_Param
End If

StrDate_Selection = "(((Run_Date) Between (#" & Start_Date & "#) And (#" & End_Date & "#)) AND "

 

[Mile-O]

When querying with dates always convert to a US Format to avoid errors.

i.e

Between Format([StartDate], "mm/dd/yyyy") And Format([EndDate], "mm/dd/yyyy")

I find it easier to create a small function for this.

Public Function QD(ByVal dteDate As Date) As String
    QD = Format(dteDate, "mm/dd/yyyy")
End Function

And then just use the following in my query:

Between QD([StartDate]) And QD([EndDate])

 

[Jibbadiah]

Thanks very much SJ... I knew it was a good idea to challenge you!!

Works with just changing the following line, and adding the function:

StrDate_Selection = "(((Run_Date) Between (#" & QD(Start_Date) & "#) And (#" & QD(End_Date) & "#)) AND "

Thanks for your input too Namliam... much appreciated guys.

 

[Jibbadiah]

Example for anyone interested

Thought I should include an example so people don't ask similar questions, and can see it working. This is Access 97 (all that is available at work).

 

[wolve]

jebbadiah & SJ

Thank you for your post very helpfull reference
thanks