Unexpected message with Applyfilter method using VBA

[SikhSioux]

Hi,
hope someone can help with this one. 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' form I get the following message:

'Appyfilter action was canceled'

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'. 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

Below is an example of what I get when I use debug.print to print the value of 'strDateCrit':
[dateIncluded] BETWEEN '24/12/2006' AND '25/12/2006'

I have tried changing the format of the 'dateIncluded' field in both forms so that it is 'short date' but this has not worked. I have also tried changing the lines:

strDateCrit = "[dateIncluded] BETWEEN " & "'" & datdateIncludedFrom & "'" & " AND " & "'" & datdateIncludedTo & "'"

to

strDateCrit = "[dateIncluded] >= " & "'" & datdateIncludedFrom & "'" & " AND " & "[dateIncluded] < " "'" & datdateIncludedTo & "'"

but the problem remains. I am completely baffled by this, appreciate any help

Cheers

 

[WayneRyan]

SS,

Change:

[dateIncluded] BETWEEN '24/12/2006' AND '25/12/2006'

To:

[dateIncluded] BETWEEN #24/12/2006# AND #25/12/2006#

Wayne