How to return all records when the user does not make a selection from a list box?

[L Ritchie]

I am using a list box to allow a user to select which region they want to see. My problem is when the user does not make a selection and wants to see all records.
This is the statement I'm using as the criteria expression in the Access query.

IIf(Not IsNull([Forms]![frm_SelectData]![lst_region]),([Forms]![frm_SelectData]![lst_region]),(Like "*"))

[NeoPa]

What do you want to see where?

[dsatino]

Unfortunately, you can't do it like this. The basic reason is this:

Behind the design view of the query, Access is building the SQL. When you put something in this criteria line Access must make a decision on what comparison operator to use in the WHERE or HAVING clause of the SQL.

By default it's going to use "=", but if comes across "Between", "Like", "Not", etc., it will use those instead.

This would seem to make your method viable on the surface, but there is a problem. Access makes it's decision on the comparison operater prior to evaluating the function you have in the criteria field.

So the SQL generated when no record is selected ends up looking something like this:

WHERE table.field = Like "*"

Which causes a syntax error or something of that sort.

Your best bet is probably to build the QueryDef or recordsource dynamically, but if it's a shared item in a multiuser environment you're going to have issues.

[NeoPa]

My question wasn't an idle one. The correct answer to this question depends entirely what the actual question is. There are at least two different scenarios I can imagine you're talking about, and each would require a very different response.

It really is very important to get your question to make sense before posting it, otherwise you can find all sorts of problems (and it wastes everybody's time).

DSatino's post handles one of the possible scenarios. Is it the one you're thinking of? Who can say.

[L Ritchie]

I'm trying to let the user select what they want to see in a number of Listboxes. From their choices I wanted to use a query to return the results. The issue is when they don't make a selection in 1 or more of the listboxes - for instance they want to see "all" regions.
It looks like I'm going to be building the query dynamically but I thought the 1st option would be quicker and more straight forward.
I was stumpped on the IIF stnt above used in the query.

Thank you.

[NeoPa]

Queries are funny things. People talk about queries, when often they are referring to QueryDef objects. A QueryDef is a stored query - A database object.

The reason I bring this up is that modifying QueryDefs is not so trivial, and opening a SQL query, that's not a QueryDef, for viewing the result set is not supported. This is where filters come in.

What I suggest you look into, is defining your object, a full report can more flexibly be handled than a simple QueryDef (whose open parameters are more restricted), which can be saved and which shows all possible records. From this point it can be opened with or without applying a filter (WhereCondition parameter). Your form can determine the requirement and, if a filter is required, build and apply it.

Does that sound like the sort of approach that would suit you?