Using MS Access/Queries


For a multi-field search form, I'm trying to create a Min and Max range for values to search for; However, I don't want want those values to be used as a search criteria if they are left blank. I got the search to work by using the BETWEEN/AND function, but if I use the Like "*" & argument, it will not work for numbers.

How can I fix this?

My criteria is as follows:
BETWEEN [Forms]![SearchF]![MinValue] AND [Forms]![SearchF]![MaxValue]

You can try to wrap your criteria inside of an IIF function to check whether or not the form fields have values, but this gets REAL complicated. The best solution is to write a custom SQL statement in VBA to set the criteria on your form. Something like:

IF ISNULL(Forms!MyForm!StartDate) THEN
MyForm.Recordsource = "SELECT * FROM MyTable WHERE [criteria without dates]"
MyForm.Recordsource = "SELECT * FROM MyTable WHERE [criteria with dates]"

I cover this extensively in my Access Search Form Seminar.

Be sure to visit my web site and watch my FREE 2-hour-long Microsoft Access Video Tutorial

Richard Rost
599CD Computer Training

Using MS Access

All Answers

Answers by Expert:

Ask Experts


Richard Rost


I am happy to answer any kinds of questions about Microsoft Access - from basic table design to advanced VBA programming. Also, please feel free to check the Access Tips & Tricks section of my web site, and free Access 101 tutorial.


I am the president of and I specialize in Microsoft Access Tutorials. I have been teaching Access in the classroom since 1994, and online since 2002.

I am a self-taught Access expert. I have been building databases for clients since the early 90s. You can see a sample of my Access Tutorials on my web site at

©2016 All rights reserved.