Complex filtering

[Guest]

I have a form that facilitates teachers entering attendance into our
database. The form sorts on teacher name, then student last name and then
course. Sounds simple enough...except for our co-op teachers. They teach both
co-op and non-co-op classes and the trouble comes from attendance records
needing to be entered in a differnt way and the form not differneciating.

I can filter co-op classes on the underlying query that populates the form.
I can filter non-co-ops likewise. I have saved both filters as queries. It's
easy enough to apply the filters to the query, but only one at a time.

Is there any way I can create a pair of command buttons that would be
visible on the form only when it was a co-op teacher entering records into it
that would allow them to apply each of the filters in turn? That way, they
could enter first co-op student attendance, for example, and then alternate
the filters and enter non-co-op students' attendance.

Or, in my convoluted thinking, overlooking a simpler solution?

Thank you in advance for any assistance offered.

 

[Guest]

If all you need to do is change the form's filtering with a command button,
just use the button's click event to change the fiter. Here is the example
from VBA Help:

Me.Filter = "Country = 'USA'"
Me.FilterOn = True

 

[Guest]

Thank you for your answer.

Unfortunately, the criteria is not so simple in our case. The filter is on
the course code field. The only consistancy in identifying a co-op class code
is the sixth character is either the number 7 or the number 8. The filter
query to pull all co-op classes, therefore, consisting of a single field:
LEFT([CLASS],6)
and then using an OR criteria:
LIKE*7 OR LIKE *8

Can I write such a complicated filter criteria into the button's CLICK event?

Thanks again.

 

[Guest]

Not a problem. The Left function will not work if you only want to look at
the character in position 6. The Left function starts with the first
character and returns as many characters as you requested. In this case, 6.
The Mid function starts with the position in the second argument and returns
as many characters as requested in the third argument.

Me.Filter = "Mid([CourseCode], 6, 1) IN('7', '8')"
Me.FilterOn = True

Thank you for your answer.

Unfortunately, the criteria is not so simple in our case. The filter is on
the course code field. The only consistancy in identifying a co-op class code
is the sixth character is either the number 7 or the number 8. The filter
query to pull all co-op classes, therefore, consisting of a single field:
LEFT([CLASS],6)
and then using an OR criteria:
LIKE*7 OR LIKE *8

Can I write such a complicated filter criteria into the button's CLICK event?

Thanks again.
--
BJM
ACE Assistant
Gary Allan High School

If all you need to do is change the form's filtering with a command button,
just use the button's click event to change the fiter. Here is the example
from VBA Help:

Me.Filter = "Country = 'USA'"
Me.FilterOn = True

 

[Guest]

Awesome!

It makes perfect sense. I guess I was not seeing the forest for the trees,
so to speak.

It is a holiday here in Canada today, but I will impliment this as soon as I
get to work in the morning.

I am confident it will do the trick, and far more elegantly than my solution
<G>

Thanks again!
--
BJM
ACE Assistant
Gary Allan High School

Not a problem. The Left function will not work if you only want to look at
the character in position 6. The Left function starts with the first
character and returns as many characters as you requested. In this case, 6.
The Mid function starts with the position in the second argument and returns
as many characters as requested in the third argument.

Me.Filter = "Mid([CourseCode], 6, 1) IN('7', '8')"
Me.FilterOn = True

Thank you for your answer.

Unfortunately, the criteria is not so simple in our case. The filter is on
the course code field. The only consistancy in identifying a co-op class code
is the sixth character is either the number 7 or the number 8. The filter
query to pull all co-op classes, therefore, consisting of a single field:
LEFT([CLASS],6)
and then using an OR criteria:
LIKE*7 OR LIKE *8

Can I write such a complicated filter criteria into the button's CLICK event?

Thanks again.
--
BJM
ACE Assistant
Gary Allan High School

If all you need to do is change the form's filtering with a command button,
just use the button's click event to change the fiter. Here is the example
from VBA Help:

Me.Filter = "Country = 'USA'"
Me.FilterOn = True


:

I have a form that facilitates teachers entering attendance into our
database. The form sorts on teacher name, then student last name and then
course. Sounds simple enough...except for our co-op teachers. They teach both
co-op and non-co-op classes and the trouble comes from attendance records
needing to be entered in a differnt way and the form not differneciating.

I can filter co-op classes on the underlying query that populates the form.
I can filter non-co-ops likewise. I have saved both filters as queries. It's
easy enough to apply the filters to the query, but only one at a time.

Is there any way I can create a pair of command buttons that would be
visible on the form only when it was a co-op teacher entering records into it
that would allow them to apply each of the filters in turn? That way, they
could enter first co-op student attendance, for example, and then alternate
the filters and enter non-co-op students' attendance.

Or, in my convoluted thinking, overlooking a simpler solution?

Thank you in advance for any assistance offered.