Unexpected reults when filtering for a date value

[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' 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 = "[dateDeactivated] BETWEEN " & "#" & datDateDeactivatedFrom & "#" & " AND " & "#" & datDateDeactivatedTo & "#"
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
__________________
The world is yours!

 

[pbaldy]

The variables you set are not the same as the variables you use.

 

[SikhSioux]

Thanks for the reply, do you know how I can make them the same?

Should I change all the date values in the table to DD/MM/YYYY HH:MM:SS format?

Is there some way I can add 00:00:00 to the date fields in the code so that a particular date value in the table say 23/05/2007 becomes 23/05/2007 00:00:00?

 

[pbaldy]

What I'm saying is that you set this:

datDateIncludedFrom

but use this

datDateDeactivatedFrom

Use the same ones you set and see what happens.

 

[SikhSioux]

Paul, I made a mistake when I typed in the code on this forum. The original code has the changes you mention i.e dateDeactivated is replaced by dateIncluded. Any idea what could be causing this?

Bob

 

[pbaldy]

Ah; I'd suggest copy/paste rather than trying to type it all in. You might be running into this:

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

 

[SikhSioux]

Thanks Paul, I'll give it a try
Bob