You are here:

Using MS Access/Access query prompt with a default to all


I'm trying to use a basic Nz([Enter value (or Enter for all)], xxx) but cannot work out what to put into xxx to get it to return all rows when the entry is null. It just returns no rows. Is there a way? I've tried Like "*", Like "[a-z]" ...

Hi Adam,

Many thanks for the question.

I hope I have understood your question correctly.

I would suggest trying the IIF function, rather than the Nz function.  You are on the right track with Like and "*"

The IIF function asks the following:

if expression = true then do this, if not do that

IIF(Expression, True part, False part)

So your criteria would look like this:

Like IIf([Enter Value (or Enter for all)] Is Not Null,[Enter Value (or Enter for all)],"*")

Hope this does the trick.

Using MS Access

All Answers

Answers by Expert:

Ask Experts


Julie Misson


My area of expertise is Microsoft Access 2000-2007. I have been building microsoft databases for the past twelve years. I would be competent in answering questions from novice Access 2000 users to the more advanced including VBA. If I cannot help, I am more than likely be able to point you to websites that can.


I have a Microsoft Access business where I build database for small businesses. These are usually one off designs, where there is no off the shelf software avaliable to meet the business needs. I also teach Microsft Access to beginners. I am the owner of the website.

Self taught in Microsoft Access. Have done some units in normalisation and SQL, but most of what I know I have learnt from books originally and more recently the Internet.

©2017 All rights reserved.