How to use wildcard in SQL query?

I have a form where users can enter a string with asterisks to perform a
wildcard search. Currently, the string entered by the user looks like this:

*somestring*

The purpose is to match any database field containing "somestring". Is
there a way to avoid the need for the asterisks? This would make it easier
for the users and also I wouldn't have to explain that asterisks are
required when performing a wildcard search.

The query looks like this:

SELECT DISTINCT Entity_ID
FROM tblMiscellaneous
WHERE (tblMiscellaneous.Misc Like QryPrm("frm0", "txtFind"));

The function looks like this:

Public Function QryPrm(ByVal strFrm As String, ByVal strCtl As String) As
Variant
QryPrm = Forms(strFrm).Controls(strCtl)
End Function

Should I just rewrite the function like this:

Public Function QryPrm(ByVal strFrm As String, ByVal strCtl As String) As
Variant
QryPrm = "*" & Forms(strFrm).Controls(strCtl) & "*"
End Function

Is there a more efficient or better way to do wildcard searches?

Thanks in advance.

re: How to use wildcard in SQL query?

In the code where you create the SQL, simply add the "*" to the value
entered by the user as:

strQry = strQry & "[SomeField] = ""*" & Me![txtUserEntryControl] & "*"""

There's no "wildcard without the wildcard characters" but you can add them
for the user.

Larry Linson
Microsoft Access MVP

"deko" <www-dot-clearpointsystems-dot-com@nospam.com> wrote in message
news:48fgd.36263$QJ3.8352@newssvr21.news.prodigy.c om...[color=blue]
> I have a form where users can enter a string with asterisks to perform a
> wildcard search. Currently, the string entered by the user looks like[/color]
this:[color=blue]
>
> *somestring*
>
> The purpose is to match any database field containing "somestring". Is
> there a way to avoid the need for the asterisks? This would make it[/color]
easier[color=blue]
> for the users and also I wouldn't have to explain that asterisks are
> required when performing a wildcard search.
>
> The query looks like this:
>
> SELECT DISTINCT Entity_ID
> FROM tblMiscellaneous
> WHERE (tblMiscellaneous.Misc Like QryPrm("frm0", "txtFind"));
>
> The function looks like this:
>
> Public Function QryPrm(ByVal strFrm As String, ByVal strCtl As String) As
> Variant
> QryPrm = Forms(strFrm).Controls(strCtl)
> End Function
>
> Should I just rewrite the function like this:
>
> Public Function QryPrm(ByVal strFrm As String, ByVal strCtl As String) As
> Variant
> QryPrm = "*" & Forms(strFrm).Controls(strCtl) & "*"
> End Function
>
> Is there a more efficient or better way to do wildcard searches?
>
> Thanks in advance.
>
>[/color]