quotes within a Where string

rienn1 · August 23rd, 2010, 09:34 PM

I've got a 'comments' field that I'd like to filter by, if desired. Problem is many of the comments include commas, quotes and/or apostrophies as a part of the literal text.

I am adding the following code to a Where string.

What is the proper syntax to do what I want?

Code:

If [Forms]![frm_SpecificInfo]![Comments] <> "*" Then
WhereStr = WhereStr & " AND ((tbl_Data_Setinfo.comments) Like """ & [Forms]![frm_SpecificInfo]![Comments] & """) "
Else: End If

allenm · August 27th, 2010, 03:04 PM

There are a couple of ways you can do this. I think what you want is anything containing the entry in the comment field of a form? Then it would be:

Code:

If [Forms]![frm_SpecificInfo]![Comments] <> "*" _
  Then WhereStr = WhereStr & " AND tbl_Data_Setinfo.comments Like ""*" _
  & [Forms]![frm_SpecificInfo]![Comments] & "*"" "

Without any wildcards a like works basically the same as =

Another way to do this is:

Code:

If [Forms]![frm_SpecificInfo]![Comments] <> "*" _
  Then WhereStr = WhereStr & " AND InStr(1,tbl_Data_Setinfo.comments,""" _
  & [Forms]![frm_SpecificInfo]![Comments] & """) > 0 "

Also I'd personally steer clear of using ! and start using . instead