creating a report based on multiple parameters from sp

[sk1hotpepr]

I have a report that I am converting to an access project, upsized from an mdb. The form contains 10 different options to let the user base their search on. For example, I have boxes for state and project type, if the user chooses just the state option, i want the recordset to return all records containing that state no matter what type, if they choose no criteria - return all records, etc. I'm not sure what is the best way to create a report based on a dynamic recordset result. I currently have a stored procedure that gets the parameters, but how do I get "all values" for a field if the user doesn't choose to narrow the criteria in that field? Is this even the best way to do this - or should I be using the On Open event in the report? Any insight into how I can get this to work would be most helpful.

 

[Minkers]

A dynamic Query seems to be what you need. I have a report based on a dynamic query myself, it works rather well and the users can leave spots blank if they wish. This Article should give you a start.

 

[sk1hotpepr]

I'm trying to create this as a stored procedure with the parameters being (state = @state or @state IS NULL), etc. When I place all options in this way, my stored procedure creates ALL options which maxes out the parameters (bad). Is there a better way to write this? I haven't tried a dynamic query, I didn't quite get what the article was trying to do. I was looking at creating a dynamic WHERE clause depending on what criteria on the form is selected (NOT NULL). Any ideas?