How to Use Access TempVar for Like Wildcard Criteria Searches
Question details
Users need to create a dynamic search form in Microsoft Access that filters different fields based on the selected button using TempVars and the Like wildcard.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Building an Access database search form with multiple criteria fields where clicking a specific search button filters records using wildcards.
- Observed behavior
- Attempting to store the 'Like *' comparison operator directly inside the TempVar causes query errors or incorrect results because the operator is treated as a literal string.
Ensure you have basic familiarity with Access VBA and SQL expressions, and verify that your form's text boxes and command buttons are correctly named.
Store Search Values Only and Construct SQL Dynamically
The most robust method is to keep the comparison operator in the VBA string and assign only the search value to the TempVar.
By keeping the 'Like' operator in the query instead of the TempVar, Access can properly evaluate the wildcards. This approach is highly effective for forms where only one field should be searched at a time depending on which button is clicked.
In your button's VBA click event, assign the text box value to a TempVar (e.g., TempVars!SearchName = Me.txtFirstName).
Construct the SQL string by wrapping the TempVar with the Like operator and wildcards: strSQL = "SELECT * FROM YourTable WHERE FirstName Like '*" & TempVars!SearchName & "*'".
Assign the generated SQL statement to the form's RecordSource property using Me.RecordSource = strSQL.
Use TempVars.Remove "SearchName" at the end of your routine to ensure previous search criteria do not interfere with future queries.
Use TempVars in a Saved Query Criteria
If you prefer to avoid writing dynamic SQL in VBA, you can directly reference TempVars within the criteria row of an Access saved query.
Looking for a Free Office Suite? Try WPS Office
While Microsoft Access handles advanced database management, if you need a free, lightweight, and highly compatible suite for your daily documents, spreadsheets, and presentations, WPS Office is an excellent alternative to Microsoft Office. It offers a familiar interface and seamless migration for your workflow.
- 1. Visit the official site: Go to the WPS Office website to find the correct version for your operating system.
- 2. Download and install: Click the download button, run the installer, and follow the simple on-screen instructions.
- 3. Open and edit seamlessly: Launch WPS Office to easily open, edit, and save your existing Microsoft Office files.
Frequently Asked Questions
Why does my query fail when I include 'Like' inside the TempVar?
When you store the 'Like' operator inside a TempVar, Microsoft Access treats the entire content as a literal string rather than an operational command. The operator and wildcards must remain in the query expression, while the TempVar should solely hold the search text.
How do I completely clear a TempVar after searching?
You can clear a specific TempVar in VBA by executing TempVars.Remove "YourVarName". To clear all active TempVars at once, use the TempVars.RemoveAll command.
Can I use TempVars to search multiple fields at the same time?
Yes, you can establish multiple TempVars for different fields. In your SQL WHERE clause or query designer, simply combine them using the 'AND' or 'OR' logical operators depending on how you want to filter the records.
