Optional Query Parameters on Forms 1

[eltel]

I need to produce a form that will allow the user to select which fields they would like to specify a criteria to search on.

For example, I may want to display all items by their category and/or within a price range.

I hope that makes some sense.

thanks in advance,

- Elliot.

 

[hellferret]

I've found the easiest way to do this is to not use Access
queries and to use SQL statements in the code.

For Example,
If you have 3 different criteria (chkA, chkB, chkC) then you can construct your SQL statement with:

'Build the SQL String
SQLS="SELECT * FROM tblTable"
If chkA or chkB or chkC then SQLS = SQLS & " WHERE"
if chkA then SQLS = SQLS & " fieldA = [valueA],"
if chkB then SQLS = SQLS & " fieldB = [valueB],"
if chkC then SQLS = SQLS & " fieldC = [valueC],"

'Trim the last comma off
SQLS = left(SQLS,len(SQLS)-1)

You can then run the query with the DoCmd.RunSQL statement.

 

[eltel]

cool, thanks for that I'll give it a go.

 

[eltel]

There is a slight problem with your solution in that you can't run a 'SELECT' style sql statement using the DoCmd.RunSql command, it will only let you perform an action query, ie update a field.

Is there perhaps a way to edit the sql code in an existing Access query, and then use the DoCmd.OpenQuery command to view the results?

 

[hellferret]

To change the SQL of an existing query:

Dim db as database, qdf as querydef

Set db = CurrentDB()
Set qdf = db.querydefs("qryQuery&quot
qdf.sql = "SELECT * FROM table"
qdf.Close


This worked in Access 2.0 (!), so you may need to play with it to get it working in anything post-stone age.