Using Like operator in VBA

[Dale Fye]

I'm working on my own version of a search and replace utility. I know Rick
Fisher has one, but lets just call this an exercise in frustration.

In this process, I want to pass a string to a subroutine, that will check
the SQL statements of all the queries in my database (I know the code below
won't accomplish this, but I intend to write another routine to analyze
query strings that I find in Form RecordSources or Control RowSource
properties, although I try to make sure that all of these are saved
queries).

As part of that subroutine, I want to pass an argument indicating whether to
look for an "exact match" or a "contains". I would like to use the Like
operator in this comparison, but cannot figure out the correct syntax.
Additionally, if searching for an exact match I want to check for a variety
of characters preceeding and following the search string (to account for the
fact that the value may be surrounded by a variety of characters (!._[ ] ,
etc.

I've used Like frequently in SQL queries, but have only recently realized
that it is available as a VBA operator, and have not found many posts that
discuss its use. Unfortunately, I have been unable to get the Like operator
to do what I want, and have resorted to using the instr( ) function as a
simple comparison.

The code looks similar to:

Private Sub SearchQueries(SearchFor as String, Optional ExactMatch as
boolean = True)

Dim qdf as dao.querydef

For each qdf in currentdb.querydefs

If ExactMatch then
IF qdf.SQL Like "*[!._(]" & SearchFor & "[!._)]*" Then
'do something
endif
else
IF qdf.SQL Like "*" & SearchFor & "*" Then
'do something
endif
endif

Next

End Sub

1. This seems to work if I replace the stuff to the right of the Like with
a literal value "*Activity_ID*", but not when I try to append wildcard
characters to a variable. What am I doing wrong?

2. Do I need to write a separate Like statement to account for no leading
characters or no trailing characters in the Exact Match case?

3. How do I add brackets "[" or "]" into the wildcard search strings?

Thanks for any help
Dale

 

[Ken Snell [MVP]]

Your syntax is correct:

If Something Like "*" & YourVariable & "*" Then


Note that the ! is a "not" operator when inside square brackets. Thus,
[!._)]
means "Not a . and Not a _ and Not a ) character".

If YourVariable contains any of the wildcard characters that are used with
Like operator, you'll need to "delimit" them with [] pairs so that they're
understood as literal and not wildcard characters. I have a function that
will do that for you:

SQLAddBrackets
http://www.accessmvp.com/KDSnell/VBA_Functions.htm#SQLAddBrkts

You can wrap your YourVariable with this function to do that for you.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

I'm working on my own version of a search and replace utility. I know
Rick Fisher has one, but lets just call this an exercise in frustration.

In this process, I want to pass a string to a subroutine, that will check
the SQL statements of all the queries in my database (I know the code
below won't accomplish this, but I intend to write another routine to
analyze query strings that I find in Form RecordSources or Control
RowSource properties, although I try to make sure that all of these are
saved queries).

As part of that subroutine, I want to pass an argument indicating whether
to look for an "exact match" or a "contains". I would like to use the
Like operator in this comparison, but cannot figure out the correct
syntax. Additionally, if searching for an exact match I want to check for
a variety of characters preceeding and following the search string (to
account for the fact that the value may be surrounded by a variety of
characters (!._[ ] , etc.

I've used Like frequently in SQL queries, but have only recently realized
that it is available as a VBA operator, and have not found many posts that
discuss its use. Unfortunately, I have been unable to get the Like
operator to do what I want, and have resorted to using the instr( )
function as a simple comparison.

The code looks similar to:

Private Sub SearchQueries(SearchFor as String, Optional ExactMatch as
boolean = True)

Dim qdf as dao.querydef

For each qdf in currentdb.querydefs

If ExactMatch then
IF qdf.SQL Like "*[!._(]" & SearchFor & "[!._)]*" Then
'do something
endif
else
IF qdf.SQL Like "*" & SearchFor & "*" Then
'do something
endif
endif

Next

End Sub

1. This seems to work if I replace the stuff to the right of the Like
with a literal value "*Activity_ID*", but not when I try to append
wildcard characters to a variable. What am I doing wrong?

2. Do I need to write a separate Like statement to account for no leading
characters or no trailing characters in the Exact Match case?

3. How do I add brackets "[" or "]" into the wildcard search strings?

Thanks for any help
Dale

 

[Dale Fye]

1. That may have been my problem, all my code had the ! as the first element
inside the [ ]. So, how do you write a if statement that tests for a bang
"!" as the character preceeding a string? If you put it as the last operator
inside the brackets will it be interpreted as a "bang" or as a "not"?

2. Don't think you answered the question of how or whether you can include
a left bracket [ inside the bracket pair to test whether the string contains
the left bracket (as in [Act_ID]). I tried doubling up on the bracket ("*[
,_.([[]" ) but that did not seem to work, although it didn't throw an error
either.

3. From my tests, it looks like I will also need to test for the case where
my search string is at the beginning or end of the test string as separate
tests.

If Something Like SearchFor & "[ ,_).]*" OR
Something Like "*[ ,_.(]" & SearchFor OR
Something Like "*[ ,_.(]" & SearchFor & "[ ,_).]*" Then
'do something
Endif

Can you think of other characters that are likely to preceed or follow a
text string in a SQL statement that you would want to test for?

----
Dale

Your syntax is correct:

If Something Like "*" & YourVariable & "*" Then


Note that the ! is a "not" operator when inside square brackets. Thus,
[!._)]
means "Not a . and Not a _ and Not a ) character".

If YourVariable contains any of the wildcard characters that are used with
Like operator, you'll need to "delimit" them with [] pairs so that they're
understood as literal and not wildcard characters. I have a function that
will do that for you:

SQLAddBrackets
http://www.accessmvp.com/KDSnell/VBA_Functions.htm#SQLAddBrkts

You can wrap your YourVariable with this function to do that for you.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

I'm working on my own version of a search and replace utility. I know
Rick Fisher has one, but lets just call this an exercise in frustration.

In this process, I want to pass a string to a subroutine, that will check
the SQL statements of all the queries in my database (I know the code
below won't accomplish this, but I intend to write another routine to
analyze query strings that I find in Form RecordSources or Control
RowSource properties, although I try to make sure that all of these are
saved queries).

As part of that subroutine, I want to pass an argument indicating whether
to look for an "exact match" or a "contains". I would like to use the
Like operator in this comparison, but cannot figure out the correct
syntax. Additionally, if searching for an exact match I want to check for
a variety of characters preceeding and following the search string (to
account for the fact that the value may be surrounded by a variety of
characters (!._[ ] , etc.

I've used Like frequently in SQL queries, but have only recently realized
that it is available as a VBA operator, and have not found many posts that
discuss its use. Unfortunately, I have been unable to get the Like
operator to do what I want, and have resorted to using the instr( )
function as a simple comparison.

The code looks similar to:

Private Sub SearchQueries(SearchFor as String, Optional ExactMatch as
boolean = True)

Dim qdf as dao.querydef

For each qdf in currentdb.querydefs

If ExactMatch then
IF qdf.SQL Like "*[!._(]" & SearchFor & "[!._)]*" Then
'do something
endif
else
IF qdf.SQL Like "*" & SearchFor & "*" Then
'do something
endif
endif

Next

End Sub

1. This seems to work if I replace the stuff to the right of the Like
with a literal value "*Activity_ID*", but not when I try to append
wildcard characters to a variable. What am I doing wrong?

2. Do I need to write a separate Like statement to account for no leading
characters or no trailing characters in the Exact Match case?

3. How do I add brackets "[" or "]" into the wildcard search strings?

Thanks for any help
Dale

 

[Ken Snell [MVP]]

Comments inline....
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

1. That may have been my problem, all my code had the ! as the first
element
inside the [ ]. So, how do you write a if statement that tests for a bang
"!" as the character preceeding a string? If you put it as the last
operator
inside the brackets will it be interpreted as a "bang" or as a "not"?

! is interpreted as "NOT" only if it's the first character inside the []
set.

2. Don't think you answered the question of how or whether you can
include
a left bracket [ inside the bracket pair to test whether the string
contains
the left bracket (as in [Act_ID]). I tried doubling up on the bracket
("*[
,_.([[]" ) but that did not seem to work, although it didn't throw an
error
either.

I did a simple test on some data that contained these values in a field:
3[45s
3
12341
3!all
3]t
31

Using
Like "*3[1[]*"
or
Like "*3[[1]*"
the comparison returns the first and last records only.


Using
Like "*3[[]*"
the comparison returns just the first record.


Using
Like "*3[[!]*"
the comparison returns the first and fourth records only.


Using
Like "*3[[!1]*"
the comparison returns the first, fourth, and last records only.


However, trying to find the ] character in a specific location as part of a
[] group is more tricky. In this case, I'd use two logic tests:
Like "*3[[!1]*" OR Like "*3]*"
This comparison returns all records except for the second record.

3. From my tests, it looks like I will also need to test for the case
where
my search string is at the beginning or end of the test string as separate
tests.

If Something Like SearchFor & "[ ,_).]*" OR
Something Like "*[ ,_.(]" & SearchFor OR
Something Like "*[ ,_.(]" & SearchFor & "[ ,_).]*" Then
'do something
Endif

Can you think of other characters that are likely to preceed or follow a
text string in a SQL statement that you would want to test for?

Might want to include semicolon and space.

----
Dale

Your syntax is correct:

If Something Like "*" & YourVariable & "*" Then


Note that the ! is a "not" operator when inside square brackets. Thus,
[!._)]
means "Not a . and Not a _ and Not a ) character".

If YourVariable contains any of the wildcard characters that are used
with
Like operator, you'll need to "delimit" them with [] pairs so that
they're
understood as literal and not wildcard characters. I have a function that
will do that for you:

SQLAddBrackets
http://www.accessmvp.com/KDSnell/VBA_Functions.htm#SQLAddBrkts

You can wrap your YourVariable with this function to do that for you.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

I'm working on my own version of a search and replace utility. I know
Rick Fisher has one, but lets just call this an exercise in
frustration.

In this process, I want to pass a string to a subroutine, that will
check
the SQL statements of all the queries in my database (I know the code
below won't accomplish this, but I intend to write another routine to
analyze query strings that I find in Form RecordSources or Control
RowSource properties, although I try to make sure that all of these are
saved queries).

As part of that subroutine, I want to pass an argument indicating
whether
to look for an "exact match" or a "contains". I would like to use the
Like operator in this comparison, but cannot figure out the correct
syntax. Additionally, if searching for an exact match I want to check
for
a variety of characters preceeding and following the search string (to
account for the fact that the value may be surrounded by a variety of
characters (!._[ ] , etc.

I've used Like frequently in SQL queries, but have only recently
realized
that it is available as a VBA operator, and have not found many posts
that
discuss its use. Unfortunately, I have been unable to get the Like
operator to do what I want, and have resorted to using the instr( )
function as a simple comparison.

The code looks similar to:

Private Sub SearchQueries(SearchFor as String, Optional ExactMatch as
boolean = True)

Dim qdf as dao.querydef

For each qdf in currentdb.querydefs

If ExactMatch then
IF qdf.SQL Like "*[!._(]" & SearchFor & "[!._)]*" Then
'do something
endif
else
IF qdf.SQL Like "*" & SearchFor & "*" Then
'do something
endif
endif

Next

End Sub

1. This seems to work if I replace the stuff to the right of the Like
with a literal value "*Activity_ID*", but not when I try to append
wildcard characters to a variable. What am I doing wrong?

2. Do I need to write a separate Like statement to account for no
leading
characters or no trailing characters in the Exact Match case?

3. How do I add brackets "[" or "]" into the wildcard search strings?

Thanks for any help
Dale

 

[Dale Fye]

Thanks, Ken.

Appreciate your feedback.

----
Dale

Comments inline....
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

1. That may have been my problem, all my code had the ! as the first
element
inside the [ ]. So, how do you write a if statement that tests for a bang
"!" as the character preceeding a string? If you put it as the last
operator
inside the brackets will it be interpreted as a "bang" or as a "not"?

! is interpreted as "NOT" only if it's the first character inside the []
set.

2. Don't think you answered the question of how or whether you can
include
a left bracket [ inside the bracket pair to test whether the string
contains
the left bracket (as in [Act_ID]). I tried doubling up on the bracket
("*[
,_.([[]" ) but that did not seem to work, although it didn't throw an
error
either.

I did a simple test on some data that contained these values in a field:
3[45s
3
12341
3!all
3]t
31

Using
Like "*3[1[]*"
or
Like "*3[[1]*"
the comparison returns the first and last records only.


Using
Like "*3[[]*"
the comparison returns just the first record.


Using
Like "*3[[!]*"
the comparison returns the first and fourth records only.


Using
Like "*3[[!1]*"
the comparison returns the first, fourth, and last records only.


However, trying to find the ] character in a specific location as part of a
[] group is more tricky. In this case, I'd use two logic tests:
Like "*3[[!1]*" OR Like "*3]*"
This comparison returns all records except for the second record.

3. From my tests, it looks like I will also need to test for the case
where
my search string is at the beginning or end of the test string as separate
tests.

If Something Like SearchFor & "[ ,_).]*" OR
Something Like "*[ ,_.(]" & SearchFor OR
Something Like "*[ ,_.(]" & SearchFor & "[ ,_).]*" Then
'do something
Endif

Can you think of other characters that are likely to preceed or follow a
text string in a SQL statement that you would want to test for?

Might want to include semicolon and space.

----
Dale

Your syntax is correct:

If Something Like "*" & YourVariable & "*" Then


Note that the ! is a "not" operator when inside square brackets. Thus,
[!._)]
means "Not a . and Not a _ and Not a ) character".

If YourVariable contains any of the wildcard characters that are used
with
Like operator, you'll need to "delimit" them with [] pairs so that
they're
understood as literal and not wildcard characters. I have a function that
will do that for you:

SQLAddBrackets
http://www.accessmvp.com/KDSnell/VBA_Functions.htm#SQLAddBrkts

You can wrap your YourVariable with this function to do that for you.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



I'm working on my own version of a search and replace utility. I know
Rick Fisher has one, but lets just call this an exercise in
frustration.

In this process, I want to pass a string to a subroutine, that will
check
the SQL statements of all the queries in my database (I know the code
below won't accomplish this, but I intend to write another routine to
analyze query strings that I find in Form RecordSources or Control
RowSource properties, although I try to make sure that all of these are
saved queries).

As part of that subroutine, I want to pass an argument indicating
whether
to look for an "exact match" or a "contains". I would like to use the
Like operator in this comparison, but cannot figure out the correct
syntax. Additionally, if searching for an exact match I want to check
for
a variety of characters preceeding and following the search string (to
account for the fact that the value may be surrounded by a variety of
characters (!._[ ] , etc.

I've used Like frequently in SQL queries, but have only recently
realized
that it is available as a VBA operator, and have not found many posts
that
discuss its use. Unfortunately, I have been unable to get the Like
operator to do what I want, and have resorted to using the instr( )
function as a simple comparison.

The code looks similar to:

Private Sub SearchQueries(SearchFor as String, Optional ExactMatch as
boolean = True)

Dim qdf as dao.querydef

For each qdf in currentdb.querydefs

If ExactMatch then
IF qdf.SQL Like "*[!._(]" & SearchFor & "[!._)]*" Then
'do something
endif
else
IF qdf.SQL Like "*" & SearchFor & "*" Then
'do something
endif
endif

Next

End Sub

1. This seems to work if I replace the stuff to the right of the Like
with a literal value "*Activity_ID*", but not when I try to append
wildcard characters to a variable. What am I doing wrong?

2. Do I need to write a separate Like statement to account for no
leading
characters or no trailing characters in the Exact Match case?

3. How do I add brackets "[" or "]" into the wildcard search strings?

Thanks for any help
Dale

 

[Dale Fye]

Ken,

Back to this issue again.

I'm trying to write a SQL statement that will exclude certain records which
contain a hyphen or an underscore in a text field.

I know I can write this like:

SELECT myTable.*
FROM myTable
WHERE myTable.TextField NOT Like "*-*"
AND myTable.textField NOT like "*_*"

but based on our earlier discussion, I thought I could write:

SELECT myTable.*
FROM myTable
WHERE myTable.TextField LIKE "*[!-_]*"

but this does not work. It returns all the records, including those that
contain a hyphen or an underscore.

----
HTH
Dale

Comments inline....
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

1. That may have been my problem, all my code had the ! as the first
element
inside the [ ]. So, how do you write a if statement that tests for a bang
"!" as the character preceeding a string? If you put it as the last
operator
inside the brackets will it be interpreted as a "bang" or as a "not"?

! is interpreted as "NOT" only if it's the first character inside the []
set.

2. Don't think you answered the question of how or whether you can
include
a left bracket [ inside the bracket pair to test whether the string
contains
the left bracket (as in [Act_ID]). I tried doubling up on the bracket
("*[
,_.([[]" ) but that did not seem to work, although it didn't throw an
error
either.

I did a simple test on some data that contained these values in a field:
3[45s
3
12341
3!all
3]t
31

Using
Like "*3[1[]*"
or
Like "*3[[1]*"
the comparison returns the first and last records only.


Using
Like "*3[[]*"
the comparison returns just the first record.


Using
Like "*3[[!]*"
the comparison returns the first and fourth records only.


Using
Like "*3[[!1]*"
the comparison returns the first, fourth, and last records only.


However, trying to find the ] character in a specific location as part of a
[] group is more tricky. In this case, I'd use two logic tests:
Like "*3[[!1]*" OR Like "*3]*"
This comparison returns all records except for the second record.

3. From my tests, it looks like I will also need to test for the case
where
my search string is at the beginning or end of the test string as separate
tests.

If Something Like SearchFor & "[ ,_).]*" OR
Something Like "*[ ,_.(]" & SearchFor OR
Something Like "*[ ,_.(]" & SearchFor & "[ ,_).]*" Then
'do something
Endif

Can you think of other characters that are likely to preceed or follow a
text string in a SQL statement that you would want to test for?

Might want to include semicolon and space.

----
Dale

Your syntax is correct:

If Something Like "*" & YourVariable & "*" Then


Note that the ! is a "not" operator when inside square brackets. Thus,
[!._)]
means "Not a . and Not a _ and Not a ) character".

If YourVariable contains any of the wildcard characters that are used
with
Like operator, you'll need to "delimit" them with [] pairs so that
they're
understood as literal and not wildcard characters. I have a function that
will do that for you:

SQLAddBrackets
http://www.accessmvp.com/KDSnell/VBA_Functions.htm#SQLAddBrkts

You can wrap your YourVariable with this function to do that for you.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



I'm working on my own version of a search and replace utility. I know
Rick Fisher has one, but lets just call this an exercise in
frustration.

In this process, I want to pass a string to a subroutine, that will
check
the SQL statements of all the queries in my database (I know the code
below won't accomplish this, but I intend to write another routine to
analyze query strings that I find in Form RecordSources or Control
RowSource properties, although I try to make sure that all of these are
saved queries).

As part of that subroutine, I want to pass an argument indicating
whether
to look for an "exact match" or a "contains". I would like to use the
Like operator in this comparison, but cannot figure out the correct
syntax. Additionally, if searching for an exact match I want to check
for
a variety of characters preceeding and following the search string (to
account for the fact that the value may be surrounded by a variety of
characters (!._[ ] , etc.

I've used Like frequently in SQL queries, but have only recently
realized
that it is available as a VBA operator, and have not found many posts
that
discuss its use. Unfortunately, I have been unable to get the Like
operator to do what I want, and have resorted to using the instr( )
function as a simple comparison.

The code looks similar to:

Private Sub SearchQueries(SearchFor as String, Optional ExactMatch as
boolean = True)

Dim qdf as dao.querydef

For each qdf in currentdb.querydefs

If ExactMatch then
IF qdf.SQL Like "*[!._(]" & SearchFor & "[!._)]*" Then
'do something
endif
else
IF qdf.SQL Like "*" & SearchFor & "*" Then
'do something
endif
endif

Next

End Sub

1. This seems to work if I replace the stuff to the right of the Like
with a literal value "*Activity_ID*", but not when I try to append
wildcard characters to a variable. What am I doing wrong?

2. Do I need to write a separate Like statement to account for no
leading
characters or no trailing characters in the Exact Match case?

3. How do I add brackets "[" or "]" into the wildcard search strings?

Thanks for any help
Dale

 

[Marshall Barton]

I'm trying to write a SQL statement that will exclude certain records which
contain a hyphen or an underscore in a text field.

I know I can write this like:

SELECT myTable.*
FROM myTable
WHERE myTable.TextField NOT Like "*-*"
AND myTable.textField NOT like "*_*"

but based on our earlier discussion, I thought I could write:

SELECT myTable.*
FROM myTable
WHERE myTable.TextField LIKE "*[!-_]*"

but this does not work. It returns all the records, including those that
contain a hyphen or an underscore.

That pattern will only fail if there is no legal characters
in the field. I.e. it will match any legal character
anywhere in the string.

Try something more like:

WHERE Not myTable.TextField LIKE "*[-_]*"