Building Search Criteria Notes

One of the first things I built in MS Access was a Search Form. Like most things, this started out as a simple Search Form and gradually got embellished. As I came to understand programming better and better, I added my new knowledge into the Search Form and I think here I have a Search Form, which is simple to use for the average user, and contains some excellent programming ideas and techniques which will be of interest to the expert programmer. My journey in creating the Search Form had a significant impact on my programming skills, the journey improve them immensely! That’s why I decided to turn it into a set of video instructions/lessons in the hope that other people would be able to benefit from my journey.

Building Search Criteria Notes

An up-to-date list of the video sets available:-

Advanced Search Form

Original question on access world forums which prompted me to create this set of videos:-

“Search Criteria”

SQL string from that original question just in case the original question is lost:-

SELECT tblMain.ClientID, tblMain.Surname, tblMain.Organization, tblMain.ProgramTitle, tblMain.City, tblMain.State, tblMain.Zip4, tblMain.Telephone
FROM tblMain
WHERE (((tblMain.Surname) Like “*” & [Forms]![frmJeffSearch]![txtSurname]) AND ((tblMain.Organization) Like “*” & [Forms]![frmJeffSearch]![txtOrganization] & “*”) AND ((tblMain.ProgramTitle) Like “*” & [Forms]![frmJeffSearch]![txtProgramTitle] & “*”) AND ((tblMain.City) Like “*” & [Forms]![frmJeffSearch]![txtCity] & “*”) AND ((tblMain.State) Like “*” & [forms]![frmJeffSearch]![cboState]) AND ((tblMain.Zip4) Like [Forms]![frmJeffSearch]![txtZip4] & “*”) AND ((tblMain.Telephone) Like “(” & [forms]![frmJeffSearch]![txtAreaCode] & “*”));

This is the SQL in a better format

				
					 SELECT tblMain.ClientID, tblMain.Surname, tblMain.Organization, tblMain.ProgramTitle, tblMain.City, tblMain.State, tblMain.Zip4, tblMain.Telephone
FROM tblMain
WHERE (((tblMain.Surname) Like "*" & [Forms]![frmJeffSearch]![txtSurname]) AND ((tblMain.Organization) Like "*" & [Forms]![frmJeffSearch]![txtOrganization] & "*") AND ((tblMain.ProgramTitle) Like "*" & [Forms]![frmJeffSearch]![txtProgramTitle] & "*") AND ((tblMain.City) Like "*" & [Forms]![frmJeffSearch]![txtCity] & "*") AND ((tblMain.State) Like "*" & [forms]![frmJeffSearch]![cboState]) AND ((tblMain.Zip4) Like [Forms]![frmJeffSearch]![txtZip4] & "*") AND ((tblMain.Telephone) Like "(" & [forms]![frmJeffSearch]![txtAreaCode] & "*"));

Building Search Criteria Notes