best way to do form-defined filter query

[GIJoeFigure]

I made a post yesterday about trying to make a DoCmd.ApplyFilter statement with a variable size wherecondition. I'm starting to wonder if thats the right approach to my problem. I really need to filter maximum 7 colums out of 30+ total columns for each tabular record and view all 30+ columns of the ones that meet the conditions I complete in the form. Any tips / ideas / warnings about what I'm trying to do? Thanks. My previous code is under the post "Using DoCmd.ApplyFilter"

Joe

J. Handfield
Interning Scientist
Eastman Kodak Research Labs
Rochester, NY 14650

 

[jimlee]

I'm not sure if this is exactly what you're trying to do but it might help.

I did a similar thing in work today, where I have about 10 fields on a continuous form and want to filter for multiple fields which the user chooses.

My solution was to provide 10 unbound text boxes in the footer of the form which the users can populate with the values they want (or use combo boxes if poss). these boxes mirrored the fields on the main form. I put a button called 'apply filter' also in the footer and put the following code in its click event...

Private Sub ApplyFilter_Click
dim filtername as string

filter = ""


if text1 <> "" then filtername = "text1 = " & "'" & text1.value & "'"
end if

'go thru fields adding to the filter string...

if text2 <> "" then filtername = filtername & "text2 = " & "'" & text2.value & "'"
end if

'etc.....
'then...

docmd.Applyfilter ,, filtername

end sub


Basically what i'm doing is testing if each text box has a value in it, and if it has then i'm adding to the filter string, otherwise i'm ignoring it. I then run the docmd.applyfilter with all the conditions in the string.

Obviously this code might need amending slightly for different types of fields eg. you wouldn't need to add all the quotation marks for numeric fields and you could use if...then... with else statement for boolean fields.

I hope this makes some sense ...


Jamie.

jimlee