filter form

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Contact Us About Resources Consent preferences Sign in

TrustPilot

starstarstarstarstar Excellent

Request Callback We will call you back  0207 987 3777 Call for assistance Your Basket Basket is empty
Callback Sign in Basket
Menu

Call us on 0207 987 3777 or Search our website

Forum home » Delegate support and help forum » Microsoft Access Training and help » Filter a form

Filter a form

resolvedResolved · Medium Priority · Version 2002/XP

Joe has attended:
Access Intermediate course

Filter a form

Dear Best STL
(Please see accompanying email Forum question - Filtering a form)

I have a form with a query as its data source.
I’m trying to apply a filter to the form in its properties (Data tab > Properties field), but no matter what I try, it doesn’t filter.
QU1 This should be a simple job, so what am I doing wrong?

QU2 I’ve managed to filter using a button with VBA attached (DoCmd.ApplyFilter), but I would like to remove the filter with another button, but again no matter what I try, I get errors. What is the code to remove a filter with VBA?

Many thanks for your help
Joe

RE: Filter a form

Hi Joe,

Thank you for the forum questions.

Question 1:

You are very close to do it right.

Change [TBL_foods]![FoodType]="fruit" to FoodType="Fruit" or [TBL_foods]![FoodType]="Fruit"

Access is case sensitive.

Question 2:

in the on click event for the second button type:

me.FilterOn=False


Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Filter a form

Hi Jens

Thanks for your answers.

Answer to question 2 resolves it :)
Answer to question 1 however doesn’t work. After having put the case sensitive filter in the form’s Filter property and open the form again, it still shows 6 records, not the 3 as expected (this is with both your examples).
Curiously when the filter is applied using VBA attached to the button, the filter does work even though ‘fruit’ is lowercase.

I was frustrated at having tried all the possibilities I could think of in the form’s Filter property, leading me to pose the forum question in the first place.
Have you tried with my sample database?

Best regards
Joe

RE: Filter a form

Hi Joe,

Yes I have tried in your sample database and it is working fine.

I have attached your database with your form and one form I have created. Both are working on my computer only showing 3 records.

One with the filter created from the form properties and one created from a form load event.

I have done it Access 2010 and everything is working.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Attached files...

Apply a filter to a form.mdb

RE: Filter a form

Hi Jens

Thanks for the answer.

Looks like filters don’t work with XP, Office 2002!
From the sample database you gave, both forms don’t filter by having either [TBL_foods]![FoodType]="Fruit" or FoodType="Fruit" in their Filter properties.
The only way a form displays filtered information on opening, is by using VBA in the On Load event (DoCmd.ApplyFilter "", "[TBL_foods]![FoodType]=""fruit""")

I’ve just tried opening your sample file on a Windows 8.1 PC with office 2010 and everything works as you said it should!

If you or your colleagues have an idea what the problem with XP, Office 2002 could be, I’d of course be interested, but I’ll close this case.

Thanks again.
Joe

 

Training courses

 

Recommend training98.7 %
recommend us
More live stats...

Training information:

See also:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

See also:
Hints and tips
Forum topics

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Access articles

Please browse our web site to find out more about
access training and other training courses.

Access tip:

Calculating The Difference Between Dates

If you wish to calculate the time between two date fields, this can be done in a number of ways:

1. As a calculated field in a query
2. As a calculated control in a form or report
3. As a calculation in a VBA procedure.

The basic syntax to get the number of days between two dates is:

=[One Date Field] - [Another Date Field]

You can also use one of the following functions:

=Month([One Date Field] - [Another Date Field])
which calculates the number of months between the two fields

=Year([One Date Field] - [Another Date Field])
which calculates the number of years between the two fields.

Another function is the DateDiff() function.

It uses an argument to determine how the time interval is measured. For example:

=DateDiff("q",[One Date Field] - [Another Date Field])
returns the number of quarters between the two fields.

Other intervals that can be used in this expression are as follows:
"yyyy" - Years
"m" - Months
"d" - Days
"w" - Weekdays
"ww" - Weeks
"h" - Hours
"n" - Minutes
"s" - Seconds

View all Access hints and tips


Request Callback

We will call you back

0207 987 3777

Call for assistance

Security verified Microsoft Partner

Formerly Best Training
© 2024 STL. All Rights Reserved.
All prices offered for business users and exclude VAT. E&OE

2nd Floor, CA House, 1 Northey Street, Limehouse Basin, London, E14 8BT. United Kingdom

Terms & Conditions Privacy & Cookie Policy Consent preferences

Server loaded in 0.07 secs.