Applyfilter method gives unexpected results when filtering for date values

[SikhSioux]

Hi I have re-posted this thread as I made a mistake in the earlier on, hope someone can help with this tricky on:

I have a form module attached to a 'search' form used to search (filter) for records on another 'main' form that shows all the records. One of the fields I can filter for is a date/time field.

When I type a date to be filtered for in the 'search' form and press the 'search' button on the 'search' some times I get records returned that have a different date to the one I typed. Below are some of the results I get:

Date being filtered Date value in table Records returned
03/01/2007 03/012007 15/03/2007 & 29/03/2007
08/01/2007 08/01/2007 No records returned
03/02/2007 03/02/2007 15/03/2007 & 29/03/2007
10/06/2007 10/06/2007 3:07:46 PM No records returned
10/06/2007 10/06/2007 3:13:47 PM No records returned
10/06/2007 10/06/2007 3:28:53 PM No records returned
10/06/2007 10/06/2007 4:10:22 PM No records returned


The state of affairs is as follows:

'Search' form: date/time field is called 'dateIncluded' and is 'unbound' its format is 'short date' and its input mask is '00/00/0000;0;

'Main' form: date/time field is called 'dateIncluded' and is bound to the 'dateIncluded' field of a table called 'tblPerson'. The format of the field in the table is 'general date'.

Some of the values in the table have format DD/MM/YYY HH:MM:SS and some have format DD/MM/YYYY.

The format of the forms field is 'general date'

The coding in the 'search' forms module run when the 'search' button on the form is clicked is:

Private Sub cmdSearchPerson_Click()

Dim strDateCrit As String
Dim datdateIncludedFrom As Date
Dim datdateIncludedTo As Date
Dim dateIncludedIsNull As Boolean

dateIncludedIsNull = True

If Not IsNull(Me!dateIncluded) Then
dateIncludedIsNull = False
datDateIncludedFrom = Me!dateIncluded
datDateIncludedTo = Me!dateIncluded + 1
strDateCrit = "[dateIncluded] BETWEEN " & "#" & datDateIncludedFrom & "#" & " AND " & "#" & datDateIncludedTo & "#"
End If

If dateIncludedIsNull Then
MsgBox "Please select / type the criteria for the search before clicking on 'Search'"
Exit Sub
End If

Me.Visible = False
Me.dateIncluded.Value = Null
DoCmd.Hourglass True
Forms!frmMainForm.SetFocus
DoCmd.ApplyFilter , strDateCrit

If Forms!frmMainForm.RecordsetClone.RecordCount = 0 Then
DoCmd.Hourglass False
MsgBox "No records meet your search criteria. Please try again"
DoCmd.ShowAllRecords
Me.Visible = True
Exit Sub
End If
DoCmd.Hourglass False

Exit Sub

I have the feeling the problem maybe something to do with the format of the date

I am completely baffled by this, appreciate any help

Cheers