finding string in other string

[gr]

Hi, I have a query which recieves a string as criteria and
finds it in other string.
To be more precise, the user gives me a Name and I find
this name in a Remarks field (which might have a message
or note for a specific person)

my code:

strSQL = "Name Like ""*"" & Forms!frmTimeChart!
ctlName& ""*"""
DoCmd.OpenReport "rptTimeChart", acViewPreview, , strSQL

This works find except when having names like Tom and
Tomas.
User Tom want to sort if there is any message for him in
the remarks field, but will get his messages and also
Tomas messages, any ideas for avoiding this situation?

 

[Jeff Boyce]

Wouldn't "Tom" be followed by a space or hyphen or something? Could you
have Tom test for "Tom "?

By the way, having a field named "Name" will give you and Access fits --
that's a reserved word in Access.

Good luck

Jeff Boyce
<Access MVP>

 

[Cheryl Fischer]

Just one little "caveat" to Jeff's suggestion ...

If, in your Remarks field, the name Tom is followed by any punctuation, it
will not be found:

"Tom reported" - will be found

"was reported by Tom." or "Tom, Jim and Jane" - will not be found

 

[gr]

Names could be separated by ":"
You mean something like this:
strSQL = "Wer Like ""*"" & Forms!frmFilters!ctlName
& "":"""

But even like above still showing for Tom and Tomas..

 

[Guest]

yap that's truth... is getting worse now..

 

[John Spencer (MVP)]

You can try the following. It should find every case where TOM preceded by a
character that is not a letter and is followed by something that is NOT a letter
between A and Z.

strSQL = """ "" & [Name] & "" "" Like ""*[!A-z]"" &
Forms!frmTimeChart!ctlName& ""[!A-Z]*"""

It will be slow since it cannot take advantange of any indexes. And there are
probably some cases where it will fail, but this should take care of the Name
being at the beginning of the field, at the end of the field, or somewhere in
the middle.