Entering VBA Replace function in query criteria

[Chris Crook]

Hey all,

I am creating a search engine that will allow a user to enter "xxxx
AND xxxxx", "xxxx NOT xxxxx", and "xxxxx OR xxxxx" into a textbox on a
form. I know very little VBA; however, I think I have a good start on
a Replace function that I can place in the criteria line of a query.
The criteria line would look like this: Like "*" & [fncReplace()] &
"*". The replace function would then look for " AND " and replace it
with: " & ""*"" AND LIKE ""*"" & "
the end result in the criteria line would then be:
Like "*" & "cat" & "*" AND LIKE "*" & "dog" & "*"

I would be sooooo grateful if anyone give provide some pointers on how
to get this thing working.

Cheers,
Chris

 

[Allen Browne]

This kind of thing is usually called "Query by Form". You should be able to
locate several threads outlining techniques and sources if you use the
Advanced Searct at groups.google.com, and search in groups:
comp.databases.ms-access; microsoft.public.assess.*

This kind of thing will not work with an existing query. You need to
generate a SQL statement dynamically, or possibly the WHERE clause of a SQL
statement (to use in the Filter of a form, or the WhereCondition of
OpenReport).

Another important consideration is that whatever interface you give your
user must provide adequate bracketing, because
a AND (b OR c)
is not the same as:
(a AND b) OR c

If you have ever performed a merge in Microsoft Word, the query interface
that it offers could serve as a model for your form.

HTH