How can I make MS Access Query Parameters Optional?

Question

I have a query that I would like to filter in different ways at different times. The way I have done this right now by placing parameters in the criteria field of the relevant query fields, however there are many cases in which I do not want to filter on a given field but only on the other fields. Is there any way in which a wildcard of some sort can be passed to the criteria parameter so that I can bypass the filtering for that particular call of the query?

Thanks in advance!

Answer 1

I don't think you can. How are you running the query?

I'd say if you need a query that has that many open variables, put it in a vba module or class, and call it, letting it build the string every time.

Answer 2

I'm not sure this helps, because I suspect you want to do this with a saved query rather than in VBA; however, the easiest thing you can do is build up a query line by line in VBA, and then creating a recordset from it.

A quite hackish way would be to re-write the saved query on the fly and then access that; however, if you have multiple people using the same DB you might run into conflicts, and you'll confuse the next developer down the line.

You could also programatically pass default value to the query (as discussed in you r previous question)

Answer 3

Well, you can return non-null values by passing * as the parameter for fields you don't wish to use in the current filter. In Access 2003 (and possibly earlier and later versions), if you are using like [paramName] as your criterion for a numeric, Text, Date, or Boolean field, an asterisk will display all records (that match the other criteria you specify). If you want to return null values as well, then you can use like [paramName] or Is Null as the criterion so that it returns all records. (This works best if you are building the query in code. If you are using an existing query, and you don't want to return null values when you do have a value for filtering, this won't work.)

If you're filtering a Memo field, you'll have to try another approach.

Answer 4

If you construct your query like so:

PARAMETERS ParamA Text ( 255 );
SELECT t.id, t.topic_id
FROM SomeTable t
WHERE t.id Like IIf(IsNull([ParamA]),"*",[ParamA])

All records will be selected if the parameter is not filled in.

Answer 5

Where are you using the query? That will determine your answer. Perhaps you don't need to define any parameters in your saved query at all.

--
David W. Fenton
David Fenton Associates

Answer 6

Back to my previous exampe in your previous question. Your parameterized query is a string looking like that:

qr = "Select Tbl_Country.* From Tbl_Country WHERE id_Country = [fid_country]"

depending on the nature of fid_Country (number, text, guid, date, etc), you'll have to replace it with a joker value and specific delimitation characters:

qr = replace(qr,"[fid_country]","""*""")

In order to fully allow wild cards, your original query could also be:

qr = "Select Tbl_Country.* From Tbl_Country _
      WHERE id_Country LIKE [fid_country]"

You can then get wild card values for fid_Country such as

qr = replace(qr,"[fid_country]","G*")

Once you're done with that, you can use the string to open a recordset

set rs = currentDb.openRecordset(qr)

Answer 7

Note the * wildcard with the LIKE keyword will only have the desired effect in ANSI-89 Query Mode.

Many people mistakenly assume the wildcard character in Access/Jet is always *. Not so. Jet has two wildcards: % in ANSI-92 Query Mode and * in ANSI-89 Query Mode.

ADO is always ANSI-92 and DAO is always ANSI-89 but the Access interface can be either.

When using the LIKE keyword in a database object (i.e. something that will be persisted in the mdb file), you should to think to yourself: what would happen if someone used this database using a Query Mode other than the one I usually use myself? Say you wanted to restrict a text field to numeric characters only and you'd written your Validation Rule like this:

NOT LIKE "*[!0-9]*"

If someone unwittingly (or otherwise) connected to your .mdb via ADO then the validation rule above would allow them to add data with non-numeric characters and your data integrity would be shot. Not good.

Better IMO to always code for both ANSI Query Modes. Perhaps this is best achieved by explicitly coding for both Modes e.g.

NOT LIKE "*[!0-9]*" AND NOT LIKE "%[!0-9]%"

But with more involved Jet SQL DML/DDL, this can become very hard to achieve concisely. That is why I recommend using the ALIKE keyword, which uses the ANSI-92 Query Mode wildcard character regardless of Query Mode e.g.

NOT ALIKE "%[!0-9]%"

Note ALIKE is undocumented (and I assume this is why my original post got marked down). I've tested this in Jet 3.51 (Access97), Jet 4.0 (Access2000 to 2003) and ACE (Access2007) and it works fine. I've previously posted this in the newsgroups and had the approval of Access MVPs. Normally I would steer clear of undocumented features myself but make an exception in this case because Jet has been deprecated for nearly a decade and the Access team who keep it alive don't seem interested in making deep changes to the engines (or bug fixes!), which has the effect of making the Jet engine a very stable product.

For more details on Jet's ANSI Query modes, see http://office.microsoft.com/en-gb/access/HP030704831033.aspx.