Microsoft Access - Fuzzy Text Search And An Empty Prompt Box

Here's an interesting dilemma....

Problem 1:

You've got a user that wants to run a report in Access and be prompted for a value to search on. Unfortunately, the thing that they are searching on is a really long string and they only want to type in part of it either due to laziness or forgetfulness.

Solution:

Create a query that the report will be based on. In the query designer type in the criteria for that column something like:

Like '*' + [Your Prompt Here] + '*'

This will look for your string inside the entire block of text.

Problem 2:

You would also like for the user to be able to just click OK on the prompt and get all of the results returned. For some reason Microsoft is thinking backwards on this one and a blank result returns no rows. Seriously, who wants to run a report with no results returned?

Solution:

In the OR section of the same row you were editing in your query designer, add the text:

[Your Same Prompt Here] is null

Now your query should perform all the functions you were looking for.


Check back tomorrow for another helpful hint from The Well Rounded Geek