how to implementing substitution variables in access 2007

[kfc]

is there a way i can use substitution variables in access 2007, i wrote a query that returns rows which have a word okeu in them but i want to use substitution variables so that i can retrieve any word i want and if the word i want is not there i want to pop a message saying word not there its easier to do it in oracle sqlplus but how can i apply it in access using sql queries??any ideas,thanks in advance

this is my sql i made in access

SELECT MENU.NAME_REC, MENU.C_REC, AUTO.C_GROUP
FROM MENU, AUTO
WHERE (((MENU.NAME_REC)="okeu"));

.

[JoeM]

Take a look at Parameter Queries. When you run the query, it will prompt you for what you want to look for at run-time.

SELECT MENU.NAME_REC, MENU.C_REC, AUTO.C_GROUP
FROM MENU, AUTO
WHERE (((MENU.NAME_REC)=[What value do you want to search for?]));

The only thing is if it does not find anything, it will return an empty set, not any sort of message. If you want something like that, it will probably require VBA.

By the way, shouldn't you have a relationship (join) between your MENU and AUTO fields? Otherwise, your query (as written) will return the Cartesian Product of the two objects.

[kfc]

thanks i noticed it does not return any message was hoping it does like some kind of validation but anyway thanks for the answer

[JoeM]

thanks i noticed it does not return any message was hoping it does like some kind of validation but anyway thanks for the answer

Queries themselves can only return data from the objects included in the data. As I said, if you want some sort of message to display, you will need to use VBA. You can create the SQL code of your query in VBA, then check to see how many records it will return, and if it is zero, you can return a message box, otherwise you can choose to return the results of the query.