When a user wants to search for specific records, the search term they enter can be incorporated directly into the SQL statement:
SQLCommand = « SELECT * FROM personen WHERE name LIKE ‘ » & _
Application.InputBox(« Which name are you searching for? ») & « ‘ »
- This query displays all records where the name field exactly matches the user input entered in the input box.
To improve flexibility, you can modify the query to search for any occurrence of the user input within the name field by using wildcards (%):
SQLCommand = « SELECT * FROM personen WHERE name LIKE ‘% » & _
Application.InputBox(« Which substring are you searching for? ») & _
« %' »
- This query returns all records where the name field contains the substring the user enters, anywhere within the field.
Important:
- The SQL command string is constructed by concatenating several parts, so do not forget the single quotes (‘) around the search term—they are crucial for correct SQL syntax.
- During development, it is helpful to display the complete SQL command using:
MsgBox SQLCommand
This helps catch common errors when inserting user input into the query. You can comment out this debug line once your code works properly.