Search sub form BETWEEN 2 dates

[jomuir]

I am the search code that Gromit kindly provided, however I want to include it to search between dates as well as the current search functionality that it has. So I have added the code below for Post Date – everything worked until I changed this from a LIKE to a BETWEEN….anyone know what I have done wrong?

Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim varColor As Variant
Dim varItem As Variant
Dim intIndex As Integer

varWhere = Null ' Main filter

' Check for LIKE Centre
If Me.txtCentre > "" Then
varWhere = varWhere & "[Centre] LIKE ""*" & Me.txtCentre & "*"" AND "
End If

' Check for LIKE Account
If Me.txtAccount > "" Then
varWhere = varWhere & "[Account] LIKE ""*" & Me.txtAccount & "*"" AND "
End If

' Check for LIKE Post Date
If Me.txtPD1 > "" Then
varWhere = varWhere & "[Posted Date] BETWEEN ""*" & Me.txtPD1 & "*"" AND ""*" & Me.txtPD2 & "*"" AND "
End If
………………….

 

[jomuir]

Got it to work by:-

' Check for LIKE Post Date
If Me.txtPD1 > "" And Me.txtPD2 > "" Then
varWhere = varWhere & "[Posted Date] BETWEEN #" & Me.txtPD1 & "# AND #" & Me.txtPD2 & "# AND "
End If

 

[jomuir]

USA date not UK

I have just started working on this again and noticed a massive problem!!

I am doing a search between 01/04/2005 & 31/03/2006 and it is returning dates from 05/01/2005 onwards!!

It appears to be converting my search date to the 4th of Jan....again if it change the date it seems to automatically pick the first number as the month unless it is over 12!!

My pc is set up as UK region, the dates are all right in my table.........is there different settings within Access, or am I doing something really basically wrong??

 

[jomuir]

Found it!!


If Me.txtPaymentDate1 > "" And Me.txtPaymentDate2 > "" Then
varWhere = varWhere & "[PaymentDate] BETWEEN #" & Format(Me.txtPaymentDate1, "mm/dd/yyyy") & "# AND #" & Format(Me.txtPaymentDate2, "mm/dd/yyyy") & "# AND "
End If

 

[DevastatioN]

Haha, this is an interesting case of you posting a forum thread, only you reply to yourself and you solve your own problem, it's quite interesting

Another way to get around this problem, is to use the calendar control in access to set the dates in the text fields, as it will supply the correct date format.

You would make a form that only contains the calendar control, which has the following code:

Private Sub xatlCalendar_Click()
'Closes the Calendar Form and returns the date entered to the original control that called the Calendar Form
Dim dtmDate As Date

dtmDate = xatlCalendar.Value
DoCmd.Close acForm, "frmCalendar", acSaveYes
Screen.ActiveControl.Value = dtmDate
End Sub

and then in both your txtPD1 and txtPD2 you would have the onclick event to be:

Private Sub txtPD1_Click()
'Opens up the calendar form so a user can select a date
DoCmd.OpenForm "frmCalendar"

If IsDate(Me.ActiveControl) Then
Forms![frmCalendar]!xatlCalendar = Me.ActiveControl
Else
Forms![frmCalendar]!xatlCalendar = Date
End If
End Sub

Just incase you're interested in using this method in the future