flexibility of selecting fields in filtering

[loisk]

Hi,

I need to create a filter form that gives a user flexibility in selection any fields in a table and in filtering records based on conditions, such as range. I've read some about a multi-select list box, but it does not give flexibility of choosing any fields. Can anybody help me on this and tell me what subject I should read? Many thanks in advance!

[I am using Access 2000 (Window XP) and ODBC connection to MySQL server table]

[sierra7]

Hi
If you are using Access have you tried the "Filter By Form" technique.

I personaly don't like it because users can filter for anything..... then not understand the results, or not be able to revert to normal form, or call for support...

However, if used intelligently it allows you to filter on multiple fields, I think including ranges with the correct syntax, without coding!

S7

[loisk]

Hi
If you are using Access have you tried the "Filter By Form" technique.

I personaly don't like it because users can filter for anything..... then not understand the results, or not be able to revert to normal form, or call for support...

However, if used intelligently it allows you to filter on multiple fields, I think including ranges with the correct syntax, without coding!

S7

Thanks for you reply, Sierra7!
In order to revert to normal form have you tried to use "Remove Filter/Sort"?

Anyway, I will look into more as you suggested and will get back. Thanks again!

[loisk]

Thanks for you reply, Sierra7!
In order to revert to normal form have you tried to use "Remove Filter/Sort"?

Anyway, I will look into more as you suggested and will get back. Thanks again!

The problem is that I need to create a filter form that executing the select query with information entered by a user in 'WHERE' clause. That's what I meant 'flexibility'. For example,

SELECT a, b, c, d, FROM mydb WHERE xx = yy

xx and yy will be entered by a user. Can anybody help on how I should approach? Thanks!

[sierra7]

Hi
Yeh, 'I' have no problem removing the filter etc but the problem I have found with some users was that they did not realise their data had been filtered, then tried browsing for something else. Anyway, that's not your problem.

Sorry to be dim but I'm a bit confused about what you mean by a 'Filter Form'
Are you suggesting that the users will enter parameters in your form to ;-

1. Display matching data in the current form ?
2. Set criteria for opening another form or report ?
3. Create an SQL string for some other purpose to use elsewhere?

Or to put to another way, when you execute your query where will the results display?

Should the users select a, b, c & d all the time or sometimes just a, b & c? (incidentally I would drop the last comma in your string or it won't work) and the WHERE can be also be on a, b, c or d ?

S7

[loisk]

Hi
Yeh, 'I' have no problem removing the filter etc but the problem I have found with some users was that they did not realise their data had been filtered, then tried browsing for something else. Anyway, that's not your problem.

Sorry to be dim but I'm a bit confused about what you mean by a 'Filter Form'
Are you suggesting that the users will enter parameters in your form to ;-

1. Display matching data in the current form ?
2. Set criteria for opening another form or report ?
3. Create an SQL string for some other purpose to use elsewhere?

Or to put to another way, when you execute your query where will the results display?

Should the users select a, b, c & d all the time or sometimes just a, b & c? (incidentally I would drop the last comma in your string or it won't work) and the WHERE can be also be on a, b, c or d ?

S7

Hi, Sierra7!

Yes, the SELECT statement shoould be the same because that's what we want to filter, but WHERE should be any column heading in the table.

We want to see a, b, c, d all the time, where the condition is changed every time. In other words we want to filter a, b, c, d by other column heading, e.g., spanish=1 and county="LA", something like this, for data analyzing purpose.
The WHERE part has to be flexible that a user can select any column heading in the table as necessary. So my question falls into 1. Thanks!

[sierra7]

Hi again Loisk
From your last posting I have picked up;

1. The WHERE can be any column heading in the table
2. Display data in current form (I presume every field (column) is shown)

Thanks for clearing the confusion because some people refer to a Filter Form as a 'dialogue' form that sets conditions for opening a Report or another form.

What you seem to want to do is set a filter on the current form but because of the flexibility you say you need then I go back to my original suggestion of using 'Filter By Form'.

Incase we are crossing purposes again, this is initiated by clicking on the small icon with a filter-funnel and a square 'form' in the background. If you are using the default menus/toolbars it is the one beside the 'funnel with a lightning bolt' in the top menu, or can also be brought up by right clicking in the centre of the form.

Oh! It has just occurred to me that you might be using UNBOUND forms with MySQL, (in which case the Access Filter By Form will not work!) so I had better stop here until you let me know.

However, I will add this reference Example Filtering on a Form

I wait until you advise you are using Bound or Unbound forms

S7

[loisk]

Hi again Loisk
From your last posting I have picked up;

1. The WHERE can be any column heading in the table
2. Display data in current form (I presume every field (column) is shown)

Thanks for clearing the confusion because some people refer to a Filter Form as a 'dialogue' form that sets conditions for opening a Report or another form.

What you seem to want to do is set a filter on the current form but because of the flexibility you say you need then I go back to my original suggestion of using 'Filter By Form'.

Incase we are crossing purposes again, this is initiated by clicking on the small icon with a filter-funnel and a square 'form' in the background. If you are using the default menus/toolbars it is the one beside the 'funnel with a lightning bolt' in the top menu, or can also be brought up by right clicking in the centre of the form.

Oh! It has just occurred to me that you might be using UNBOUND forms with MySQL, (in which case the Access Filter By Form will not work!) so I had better stop here until you let me know.

However, I will add this reference Example Filtering on a Form

I wait until you advise you are using Bound or Unbound forms

S7

Hi,

I am using Bound form connected to MySQL table via ODBC.
Thank you so much, sierra7!

[sierra7]

Hi,

I am using Bound form connected to MySQL table via ODBC.
Thank you so much, sierra7!

That's good! The reason that I am banging on about Filter By Form is that you said you wanted flexibility; to filter on any field; to state ranges and I think you hinted about multi-select (By which I mean not just filter by "Florida" but "Florida and New Mexico")

And I believe you can do all this using Filter By Form withou writing a line of code!

You users will have to learn certain rules of syntax for > , < , BETWEEN, LIKE and IN etc

If you read the topic in Help it should be self explanatory but let's see if this is what you want first.

S7

[loisk]

That's good! The reason that I am banging on about Filter By Form is that you said you wanted flexibility; to filter on any field; to state ranges and I think you hinted about multi-select (By which I mean not just filter by "Florida" but "Florida and New Mexico")

And I believe you can do all this using Filter By Form withou writing a line of code!

You users will have to learn certain rules of syntax for > , < , BETWEEN, LIKE and IN etc

If you read the topic in Help it should be self explanatory but let's see if this is what you want first.

S7

Thanks for your prompt reply!
I did read about Multi-select listbox or so, but it's still not what I am looking for.
I need to be able to select not just by value in a column, but should be able to select any column heading at filtering. I am not sure how I put it in better way in explanation. Multi-select box is flexible in selecting multi values in a column. To reiterate, in WHERE clause, for instance, in where column=value this column should be chosen by a user at the time a user filters.
I hope I am not making you irritated by my being inefficient in describing the problem.

[sierra7]

That's exactly what Filter By Form allows!

You can filter by any or all fields! Even putting a range in the field (with the correct syntax)

You had better give more detailed examples because I can not see where we differ.

S7

[loisk]

That's exactly what Filter By Form allows!

You can filter by any or all fields! Even putting a range in the field (with the correct syntax)

You had better give more detailed examples because I can not see where we differ.

S7

Hi again!

Now I see what I was missing in describing my problem!
I know that the Filter By Form can do this, but the difference is that...
The displayed fields are only a few, like 4 or 5 columns out of more than 20, and selecting column will be any column in those 20 plus columns that are not shown in the form. Hope that I am making it clearer than before.

[sierra7]

That makes life difficult !

I think in this case you will need to create a new form with 20 or so unbound TextBoxes each representing one of your fields, then within these boxes you set up you criteria string and with a command button you open the form you have been referring to

 
DoCmd.OpenForm "frmThatsBeenReferredTo", , , strCriteria
 

Setting up the string is relatively easy. My preference is to declare a string variable for each of the terms to be involved in the filter. I'll call them s1, s2, s3 etc but it is usually more helpful to represent the fieldnames. Then

strCriteria = s1 & s2 & s3 ' . . . etc
DoCmd.OpenForm "frmThatsBeenReferredTo", , , strCriteria

Obviously s1 must be somthing like "[ID]>1" so the term is always present, then s2 can be "and [LangID]=" & Me.txtLangID, or if you are not filtering on LangID then s2="". Get the idea? You will have to be careful setting up you criteria terms as to which are text values, which are numbers and which are dates to get the correct syntax.

I normally set all the terms (except first) to blank and then adjust them on the After_Update event of the unbound text box. So if there was a box called txtLangID the code would be something like;

 If Me!txtLangID = "" Then 
s2 =""
Else
s2 = "and [LangID] =" & Me!txtLangID
EndIf
 

Get the basic idea working on a few fields then come back if there are any problems.

I think that with the references already given you should be alright. I would leave the more ambitious 'multi-select' aspect until the basics work ok.
S7