Problem with a date filter combo box

D

dsajones

Registered User.
Local time
Today, 22:08
Joined
Jan 22, 2011
Messages
47
Hi,

I've reposted this as I originally didn't enclose the code properly within code tags.

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

Code: 
[/FONT][/COLOR]
[COLOR=black][FONT=Arial][FONT=Arial][SIZE=3]Q_UpdateRecords[/SIZE][/FONT][/FONT][/COLOR][COLOR=black][FONT=Verdana]

[/FONT][/COLOR][COLOR=black][FONT=Arial][FONT=Arial][SIZE=3]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[/SIZE][/FONT][/FONT][/COLOR][COLOR=black][FONT=Verdana]
[/FONT][/COLOR][COLOR=black][FONT=Arial][FONT=Arial][SIZE=3]FROM T_CallDetails[/SIZE][/FONT][/FONT][/COLOR][COLOR=black][FONT=Verdana]
[/FONT][/COLOR][COLOR=black][FONT=Arial][FONT=Arial][SIZE=3]WHERE (((T_CallDetails.CallDate)>=Date()-3))[/SIZE][/FONT][/FONT][/COLOR][COLOR=black][FONT=Verdana]
[/FONT][/COLOR][COLOR=black][FONT=Arial][FONT=Arial][SIZE=3]ORDER BY T_CallDetails.custkey, T_CallDetails.CallDate;[/SIZE][/FONT][/FONT][/COLOR][COLOR=black][FONT=Verdana]

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!!


Code: 
[/FONT][/SIZE][/FONT][/COLOR]
[COLOR=black][FONT=Arial][FONT=Arial][SIZE=3]Private Sub cboCustomer_AfterUpdate()[/SIZE][/FONT][/FONT][/COLOR][COLOR=black][FONT=Verdana]
[/FONT][/COLOR][COLOR=black][FONT=Arial][FONT=Arial][SIZE=3]Call FilterJobs[/SIZE][/FONT][/FONT][/COLOR][COLOR=black][FONT=Verdana]
[/FONT][/COLOR][COLOR=black][FONT=Arial][FONT=Arial][SIZE=3]End Sub[/SIZE][/FONT][/FONT][/COLOR][COLOR=black][FONT=Verdana]

[/FONT][/COLOR][COLOR=black][FONT=Arial][FONT=Arial][SIZE=3]Private Sub cboDateFilter_AfterUpdate()[/SIZE][/FONT][/FONT][/COLOR][COLOR=black][FONT=Verdana]
[/FONT][/COLOR][COLOR=black][FONT=Arial][FONT=Arial][SIZE=3]Call FilterJobs[/SIZE][/FONT][/FONT][/COLOR][COLOR=black][FONT=Verdana]
[/FONT][/COLOR][COLOR=black][FONT=Arial][FONT=Arial][SIZE=3]End Sub[/SIZE][/FONT][/FONT][/COLOR][COLOR=black][FONT=Verdana]

[/FONT][/COLOR][COLOR=black][FONT=Arial][FONT=Arial][SIZE=3]Private Sub cboJobType_AfterUpdate()[/SIZE][/FONT][/FONT][/COLOR][COLOR=black][FONT=Verdana]
[/FONT][/COLOR][COLOR=black][FONT=Arial][FONT=Arial][SIZE=3]Call FilterJobs[/SIZE][/FONT][/FONT][/COLOR][COLOR=black][FONT=Verdana]
[/FONT][/COLOR][COLOR=black][FONT=Arial][FONT=Arial][SIZE=3]End Sub[/SIZE][/FONT][/FONT][/COLOR][COLOR=black][FONT=Verdana]


[/FONT][/COLOR][COLOR=black][FONT=Arial][FONT=Arial][SIZE=3]Public Sub FilterJobs()[/SIZE][/FONT][/FONT][/COLOR][COLOR=black][FONT=Verdana]

[/FONT][/COLOR][COLOR=black][FONT=Arial][FONT=Arial][SIZE=3]Dim strFilter As String[/SIZE][/FONT][/FONT][/COLOR][COLOR=black][FONT=Verdana]

[/FONT][/COLOR][COLOR=black][FONT=Arial][FONT=Arial][SIZE=3]'The following is a default filter (always true!) just in case the combo selections are blank.[/SIZE][/FONT][/FONT][/COLOR][COLOR=black][FONT=Verdana]
[/FONT][/COLOR][COLOR=black][FONT=Arial][FONT=Arial][SIZE=3]'It also means we don't have to deal with getting rid of unwanted "AND" in our filter[/SIZE][/FONT][/FONT][/COLOR][COLOR=black][FONT=Verdana]

[/FONT][/COLOR][COLOR=black][FONT=Arial][FONT=Arial][SIZE=3]strFilter = "1=1"[/SIZE][/FONT][/FONT][/COLOR][COLOR=black][FONT=Verdana]

[/FONT][/COLOR][COLOR=black][FONT=Arial][FONT=Arial][SIZE=3]If Nz(Me.cboCustomer, "") <> "" Then[/SIZE][/FONT][/FONT][/COLOR][COLOR=black][FONT=Verdana]
[/FONT][/COLOR][COLOR=black][FONT=Arial][FONT=Arial][SIZE=3]strFilter = strFilter & " and [custKey]=" & Me.cboCustomer[/SIZE][/FONT][/FONT][/COLOR][COLOR=black][FONT=Verdana]
[/FONT][/COLOR][COLOR=black][FONT=Arial][FONT=Arial][SIZE=3]End If[/SIZE][/FONT][/FONT][/COLOR][COLOR=black][FONT=Verdana]

[/FONT][/COLOR][COLOR=black][FONT=Arial][FONT=Arial][SIZE=3]If Nz(Me.cboJobType, "") <> "" Then[/SIZE][/FONT][/FONT][/COLOR][COLOR=black][FONT=Verdana]
[/FONT][/COLOR][COLOR=black][FONT=Arial][FONT=Arial][SIZE=3]strFilter = strFilter & " and [job_Type]='" & Me.cboJobType & "'"[/SIZE][/FONT][/FONT][/COLOR][COLOR=black][FONT=Verdana]
[/FONT][/COLOR][COLOR=black][FONT=Arial][FONT=Arial][SIZE=3]End If[/SIZE][/FONT][/FONT][/COLOR][COLOR=black][FONT=Verdana]

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

[/FONT][/COLOR][COLOR=black][FONT=Arial][FONT=Arial][SIZE=3]End If[/SIZE][/FONT][/FONT][/COLOR][COLOR=black][FONT=Verdana]

[/FONT][/COLOR][COLOR=black][FONT=Arial][FONT=Arial][SIZE=3]Me.Filter = strFilter[/SIZE][/FONT][/FONT][/COLOR][COLOR=black][FONT=Verdana]
[/FONT][/COLOR][COLOR=black][FONT=Arial][FONT=Arial][SIZE=3]Me.FilterOn = True[/SIZE][/FONT][/FONT][/COLOR][COLOR=black][FONT=Verdana]

[/FONT][/COLOR][COLOR=black][FONT=Arial][SIZE=3][FONT=Arial]End Sub[/FONT][/SIZE][/FONT][/COLOR]
[COLOR=black][FONT=Arial][SIZE=3][FONT=Arial]

Can anyone see what I'm doing wrong??

Cheers
David
 
You could have done it in your other thread by editting the post and adding the code tags or adding a new post in that thread.

This seems redundant:
Code: 
strFilter = "1=1"

How about this:
Code: 
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
    
    If Nz(Me.cboCustomer, "") <> "" Then
        strFilter = "[custKey] = " & Me.cboCustomer
    End If
    
    If Nz(Me.cboJobType, "") <> "" Then
        If Len(strFilter) <> 0 Then
            strFilter = strFilter & " AND [job_Type]='" & Me.cboJobType & "'"
        Else
            strFilter = "[job_Type]='" & Me.cboJobType & "'"
        End If
    End If
    
    If Nz(Me.cboDateFilter, "") <> "" Then
        If Len(strFilter) <> 0 Then
            strFilter = strFilter & " AND [CallDate] = " & Format(Me.cboDateFilter, "\#mm/dd/yyyy\#")
        Else
            strFilter = "[CallDate] = " & Format(Me.cboDateFilter, "\#mm/dd/yyyy\#")
        End If
    End If
    
    Me.Filter = strFilter
    Me.FilterOn = True

End Sub
 
Hi vbaInet,

Thanks for getting back to me. I tried to edit the original message and couldn't see how. Anyway, the

Code: 
strFilter = "1=1"

was suggested by another contributor to guard against nulls or empty strings being passed to the filter i.e. strfilter will always have a value in it which is true. I understand what you're saying, however that's not the problem I'm trying to solve. It's the date filter not working. Because the date is stored as a full date/time even though I have a format filter for short date in both the table and the form, I have to use BETWEEN and add 00:00:00 and 23:59:59 to ensure I capture every record that has a date of the required date.

This is a database that I've taken over responsibility for and I'm very new to the world of Access programming.

Cheers
David
 
dsajones said:
Thanks for getting back to me. I tried to edit the original message and couldn't see how.
Click to expand...
Just below your post you will see a button captioned Edit.

dsajones said:
Anyway, the

Code: 
strFilter = "1=1"
was suggested by another contributor to guard against nulls or empty strings being passed to the filter i.e. strfilter will always have a value in it which is true.
Click to expand...
I don't see how this safeguards against Nulls or zero-length strings being passed to the Filter. strFilter is dimensioned as a String so it can't ever contain Null. If you really want to be sure that strFilter doesn't contain a zls before applying the filter, use the Len() function. You don't happen to know the contributor's username?

dsajones said:
I understand what you're saying, however that's not the problem I'm trying to solve. It's the date filter not working. Because the date is stored as a full date/time even though I have a format filter for short date in both the table and the form, I have to use BETWEEN and add 00:00:00 and 23:59:59 to ensure I capture every record that has a date of the required date.
Click to expand...
Have you actually tried the code I re-wrote? The way I formatted the date isn't the same as any of the ones you've tried. You don't need a Time factor because the Jet engine is clever enough to pick the correct date range.
 
Ah yes, I can see the edit button now, thanks.

Yes, I did try your code and I get the same result. Filtering by Customer or Job Type works fine but not that date.

The original code with strFilter "1=1" was given to me by stopher.

Cheers
David
 
If you upload a stripped down version of your db with some sample records I will be able to advice a fix. Also mention the name of the problem form.

Ah, Chris (aka Stopher), I will probably ask him about this later.
 
OK thanks. I'll get a copy of the live database and strip out all that's not relevant.

Cheers
David
 
Hi. Here's the database. The form is the only one there. Just realised that the code you supplied me is in there but I have edited the line for the date filter and made it a 'between' rather than equal to. But that's only becuase ito didn't work the first way.

Cheers
David
 

Attachments

dsajones said:
I have to use BETWEEN and add 00:00:00 and 23:59:59 to ensure I capture every record that has a date of the required date.
Click to expand...

Rather than compare the times it is more efficient to strip the time from the Date/Time field before the comparison.

Date/Tiime values are actually numbers that Access displays as a Date Time. The integer part is the number of days since 30 December 1899 while the fractional part is the time as fractional parts of a day. The value is processed as a number and supports all artihmetic operations, comparisons and functions.

Simply apply the Int() function to the record value to get the date component. This result can be compared directly against a Date/Time value as in:

WHERE Int([DateTimeField]) > #1/1/2011#
 
dsajones said:
Code: 
strFilter = "1=1"
was suggested by another contributor to guard against nulls or empty strings being passed to the filter i.e. strfilter will always have a value in it which is true.
Click to expand...

I think stopher's intent might have been misunderstood.

By starting with an always True statement, any additional filters can be appended to the string without concern for separately adding AND (or OR) between the filter substrings. The coordinator (I assume that is what these words called in SQL, as in English) is simply included at the beginning of every appended string.

Otherwise the coordinator must be selectively added only after the first term is applied. This technique is frowned upon by some developers.

Some prefer to include it at the end of each term then strip the last one. Others add the coordinator in a separate conditional step.
 
I didn't really put much thought into it but I can now see what the "1=1" actually does, thanks GalaxiomAtHome.

I'm one of those people that will use a conditional step. If I can effectively do it in code I won't want to add an extra criteria to my filter. Yes, the "1=1" might have a miniscule effect but at least it still has some effect.

So have you tried what GalaxiomAtHome suggested?

Also, see attached - using DateValue()
 

Attachments

Hi both.

GlaxiomAtHome - Thanks for your help. I did start having a play with your solution but was having difficulty getting the syntax right. Your example is shown as the Where clause in a query. Should it work as part of a filter on a form?

vbaInet - Thanks, DateValue() works a treat. In my original attempts I tried using DatePart and that didn't work at all. I don't think I ever came across DateValue. Anyway, that's solved my problem so thanks very much.

Cheers
David
 
Glad to hear that's resolved your problem.

By the way, if you're not using the Time part of Date/Time field, it would be worth changing the default value to Date() to store only the Date part.
 
dsajones said:
GlaxiomAtHome - Thanks for your help. I did start having a play with your solution but was having difficulty getting the syntax right. Your example is shown as the Where clause in a query. Should it work as part of a filter on a form?
Click to expand...

Should work as a filter. Just drop the "WHERE".
 
vbaInet said:
Glad to hear that's resolved your problem.

By the way, if you're not using the Time part of Date/Time field, it would be worth changing the default value to Date() to store only the Date part.
Click to expand...

Yes, I did look at doing that but there are lots of reports we produce for the end clients and because of the nature of the service we provide, the reports run from a date/time to another date/time for example, at a weekend the reports would run from 5pm Friday to 9am Monday.

Thanks again for your help
 
You must log in or register to reply here.

Similar threads

T
Filter Form with multiple Unbound combo box and dates
Replies
14
Views
1,951
MajP
MajP
T
Solved It takes too long to download data from server
Replies
16
Views
1,394
GPGeorge
GPGeorge
I
Filter between two data
Replies
3
Views
724
sonic8
S
A
FILTER PRINT REPORT
Replies
2
Views
631
arnelgp
arnelgp
J
Match the primary Key in the Main form to the foreign key in the Subform
Replies
9
Views
342
MajP
MajP

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom