Date Filter combo not working

[dsajones]

Hi,

I’m having a problem with some form filters that were working OK. I’m loading a multi-record form based on a query:

Q_UpdateRecords

SELECT T_CallDetails.[Reference Number], T_CallDetails.custkey, T_CallDetails.CallDate, T_CallDetails.Name, T_CallDetails.Address, T_CallDetails.[Call In Time], T_CallDetails.[Call Out Time], T_CallDetails.Problem, T_CallDetails.Practice, T_CallDetails.[Deceased Information], T_CallDetails.[Doctor Paged], T_CallDetails.Urgent, T_CallDetails.Contacted, T_CallDetails.Chargable, T_CallDetails.code, T_CallDetails.Telephone, T_CallDetails.Duty, T_CallDetails.Job_Passed, T_CallDetails.B19, T_CallDetails.Job_Type
FROM T_CallDetails
WHERE (((T_CallDetails.CallDate)>=Date()-3))
ORDER BY T_CallDetails.custkey, T_CallDetails.CallDate;

So this pulls in all records with a Call Date within the last 4 days

On the form I have 3 combo boxes.

cboDateFilter is populated when the form loads with the following data:
SELECT DISTINCT format(Q_UpdateRecords.callDate,"short date") FROM Q_UpdateRecords;

This correctly populates the combo box with todays date and the past 3. But when I select one of the dates, it filters out all records. The filters on Customer and Job Type are working perfectly. I’m sure the date filter was working OK before I went on 2 weeks leave!!

Private Sub cboCustomer_AfterUpdate()
Call FilterJobs
End Sub

Private Sub cboDateFilter_AfterUpdate()
Call FilterJobs
End Sub

Private Sub cboJobType_AfterUpdate()
Call FilterJobs
End Sub


Public Sub FilterJobs()

Dim strFilter As String

'The following is a default filter (always true!) just in case the combo selections are blank.
'It also means we don't have to deal with getting rid of unwanted "AND" in our filter

strFilter = "1=1"

If Nz(Me.cboCustomer, "") <> "" Then
strFilter = strFilter & " and [custKey]=" & Me.cboCustomer
End If

If Nz(Me.cboJobType, "") <> "" Then
strFilter = strFilter & " and [job_Type]='" & Me.cboJobType & "'"
End If

If Nz(Me.cboDateFilter, "") <> "" Then
'strFilter = strFilter & " and [date]=" & Me.cboDateFilter & ""
'strFilter = strFilter & " and format([CallDate], 'mm/dd/yyyy') = " & Format(Me.cboDateFilter, "\#mm/dd/yyyy\#")
'strFilter = strFilter & " and CallDate = " & Format(Me.cboDateFilter, "\#dd/mm/yyyy\#")
strFilter = strFilter & " and CallDate between " & Format(Me.cboDateFilter, "\#dd/mm/yyyy") & " 00:00:00# and" & Format(Me.cboDateFilter, "\#dd/mm/yyyy") & " 23:59:59#"
'strFilter = strFilter & " and [q_updaterecords.Date] = Me.cboDateFilter"

End If

Me.Filter = strFilter
Me.FilterOn = True

End Sub


Can anyone see what I'm doing wrong??

Cheers
David

 

[vbaInet]

Please enclose your code in Code Tags so it's more readable. Here's how:

http://www.access-programmers.co.uk/forums/showthread.php?p=1009015#post1009015

 

[dsajones]

Sorry about the missing code tags. It's the first time I've posted any code like this. I've tried to go back in and edit the message to put the code tags in but that results in loads of font tags appearing - looks a little like HTML. Should I delete and start again?

Cheers
David

 

[vbaInet]

In your next thread just type it in like this:
[c o d e]
... Put your code here
[/c o d e]

Without the spaces in code.