Search 2.0 - Search Form 2.0 in Microsoft Access


	Back to Search Form 2.0 Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Search 2.0

Larry Fisackerly

4 years ago

When I enter a search term, I get not only the search term, but also all the records with blanks in the searched in field. The only search that works is FirstName and LastName. See screenshots. I searched for "Aetna" in Company. There are about 30 records with Company = Aetna out of the 900 total records. The search returns all 900 record--30 with Aetna and 870 without Aetna. I have the same problem with Phone_Mobile, Plan_Type, and Email_Home. Seems like the "Or Is Null" is showing all records in the search field.

	Larry Fisackerly OP @Reply 4 years ago

	Larry Fisackerly OP @Reply 4 years ago

	Scott Axton @Reply 4 years ago
If you don't want to see the records with blanks you need to change the criteria to AND IS NOT NULL

	Larry Fisackerly OP @Reply 4 years ago
Richard's video uses OR IS NOT NULL. He explained in the video you had to use OR IS NOT NULL in criteria for each field.

	Larry Fisackerly OP @Reply 4 years ago

	Scott Axton @Reply 4 years ago
True but you just said you don't want to see blank records. The OR IS NULL tells Access "show me all of the Aetna plus any record that doesn't have anything" (NULL). The other alternative is to make sure Company is never blank in your table. You would have to set is for allow zero length to no and make it a required field.

	Scott Axton @Reply 4 years ago
In the full Search Seminar Richard shows you how you can add a box that you can specify whether or not to include or exclude null values for each field.

	Richard Rost @Reply 4 years ago
Scott is correct. In this search form, you'll see records that match your search OR are null. It requires more complex VBA in order to change it.

	Larry Fisackerly OP @Reply 4 years ago
Thanks so much. Is the "more complex" VBA covered in the Extended Cut?

	Scott Axton @Reply 4 years ago
No - not covered in this video's extended cut. To the best of my recollection the only place it was covered is in the Search Seminar I linked above. (Richard - correct me if I'm wrong)

	Richard Rost @Reply 4 years ago
Scott is correct. It's basically building an entire SQL string with WHERE and ORDER BY conditions straight in VBA from the ground up. If you don't want the full Search Seminar, I do cover something similar in Access Developer 26, but then again that builds upon material from the previous lessons too.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Search Form 2.0.

Next Unseen

New Feature: Comment Live View