Query with Optional Criteria

[risoam]

I am trying to write a query that reads data from one tables based off of criteria on a form. All of the criteria fields are optional. If no criteria was specified then all records should be returned. Any and all criteria is optional and should return all unless specified. Does anyone know how to do this? Thanks.

 

[GoDawgs]

You need to build an SQL statement in VBA to do this. Basically you'd set up the query and then add criteria if the control isn't blank...something like this:

SQLstring= "Select * FROM TheTable"
if txtPlace <>&quot;&quot; then
SQLstring=SQLstring & &quot; WHERE Place = &quot; & txtplace
end if

That's a very basic set up of what you're asking...hope that helps.

Kevin

 

[risoam]

I see, but I have a few fields that this applies to. Can I do this in an Access Query? Or do I have to build it outside of it? Thanks.

 

[GoDawgs]

I don't know of a way to do it in a query. You can still use code for a few fields, it's just more complicated since you can only add &quot;WHERE&quot; once and other little things like that. The code isn't that hard, but it's definitely more difficult than just creating a query. Maybe someone else has a better idea for you...sorry.

Kevin

 

[risoam]

I found one way to do it.

iif(isnull([form].[fieldname]),[tablename].[fieldname],[form].[fieldname])

This works but it seems a bit slow...