FILTERING

[MICHELE]

When I filter records on a form, does it filter the actual Record source (table or query)? I need to display all fields of the filtered records only.
Thanks in advance.

 

[zynn]

Hi, i am not sure if this helps.
The filter doesn't change the actual table. It just changes the query for the RecordSource. From what i understand, the RecordSource is also just a sql statement that retrieves everything from the table. ie. "Select * from Clients_table"
So, in my case i just replace it with a new sql statement with all the filter information. ie. "Select * from Clients_table WHERE Client_group LIKE 'Individual' ".
As a result, the record count at the bottom of the page will be reduced and you should be seeing only the filtered records.

Good luck.

 

[MICHELE]

I will try that, but where do I go to put the SQL statement. I haven't had much luck when writing SQL.
Thanks!

 

[zynn]

there are 2 ways to do it.

Sql method.
After you decided your filter criteria. Depending on when you want the filter to start, place it in a button_click or a form_load event procedure.
The code might go something like this :
sql = "Select * from Clients where client_id > 100 "
Form.RecordSource = sql
Meaning: get only records with client_id greater than 100.
note: client_id is a field in the table.

Filter method.
this uses the inbuilt filter method of access forms.
Similarly, depending on where you want the filter to start.
The code would go something like this.

filterstring = client_id > 100
Me.Filter = filterstring
Me.FilterOn = True

note: to remove the filter set it to Me.FilterOn = False

Don't hesitate to post a reply if i didn't answer your question correctly.