Help with VBA replace function

[Chris Crook]

Hey everyone,

I have just started using VBA in Access like a week ago, because I am
sure it is the only way to make my database work. What I am trying to
do is create a function that will be within the criteria of a query.
I am trying to write a replace function where a user can type in two
words to search separated by AND. The replace function will then look
for " AND ", replacing it with
(" & ""*"" AND LIKE ""*"" & "). The whole criteria line will look
like this:
Like "*" & [fncReplacetxt()] & "*".

My code for the replace function looks like this (it is probably
wrong, cause I just started writing code yesterday):

Public Function ReplaceTxt(inputtxt As String) As String
Dim inputtxtAnd As String
Dim inputtxtNot As String
Dim inputtxtOr As String

inputtxt = Replace(inputtxt, " AND ", " & ""*"" AND LIKE ""*"" &
")
inputtxtAnd = Replace(inputtxtAnd, " NOT ", " & ""*"" NOT LIKE
""*"" & ")
inputtxtNot = Replace(inputtxtNot, " OR ", " & ""*"" OR LIKE ""*""
& ")
ReplaceTxt = inputtxtOr
End Function

I know it's not, but assuming this function is OK, how can I get the
text entered by a user on a form to go thru this function and be
implanted in the query criteria??

Any help is greatly appreciated!

Cheers,
Chris

 

[Albert D. Kallal]

Hey everyone,

I have just started using VBA in Access like a week ago, because I am
sure it is the only way to make my database work. What I am trying to
do is create a function that will be within the criteria of a query.
I am trying to write a replace function where a user can type in two
words to search separated by AND.

If you need to ask the user for some input, and then execte a update..you
don't want to use some sql with some function embbe3d inside of it.

Just make the reprot (or form) for the query based on a plan sql query with
NO condistion, or NO parmates. Get rid of all the junk parmamre stuff in the
sql query.

Then, just build a nice little prompt form, and place two text box contorls
on the screen for the two promtps, lets call the two boxes

txtParm1 and txtParm2.

The code, then can be:

Dim strWhere As String


If IsNull(Me.txtParm1) = False Then
strWhere = "( CompanyName like *'" & Me.txtParm1 & "*' )"
End If

If IsNull(Me.txtParm2) = False Then


If strWhere <> "" Then
strWhere = strWhere & " or "
End If

strWhere = "( CompanyName like *'" & Me.txtParm2 & "*' )

End If

' now launch the reprot

DoCmd.OpenReport "yourReprot", acViewPreview, , strWhere

The above code is quite clean, and it also means that the parameters are
optional....and if nothing is entered, then the txt box is ignored. Further,
it also means that you mess up the nice sql statement..and it can be used
for additional forms, and additional reports. If you start putting a bunch
of parameters and stuff that is attached to a particular form, then that
query becomes ONLY useful for that one report (or form) when the prompt
screen is open. So, as a general rule, I simply write some code to make nice
report prompt screens. The above exact code idea was used in thee screen
shots (take a look...might give you some ideas for prompt screens).
http://www.attcanada.net/~kallal.msn/ridesrpt/ridesrpt.html

Also, are you sure you want to use something such as:

CompanyName like "*West*"

The above cannot be indexed, and thus will be slow. I fyou ahv a small file
(only a thousand or so reocrds..then the above would be an accepatlzbe..but
with larger files...I would use catuion. You might be better off to match
the start...such as:

CompanyName like "West*"

The above will match any company that starts with West...and can use high
speed indexing.