Re: Using Like operator in VBA
- From: Dale Fye <dale.fye@xxxxxxxxxx>
- Date: Wed, 9 Dec 2009 10:31:01 -0800
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
"Ken Snell [MVP]" wrote:
Comments inline.....
--
Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/
"Dale Fye" <dale.fye@xxxxxxxxxx> wrote in message
news:DDB2144A-AC6C-498F-A1F4-ABA5F72EE1B5@xxxxxxxxxxxxxxxx
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
"Ken Snell [MVP]" wrote:
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/
"Dale Fye" <dale.fye@xxxxxxxxxx> wrote in message
news:%23CvBXAnMKHA.220@xxxxxxxxxxxxxxxxxxxxxxx
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
- Follow-Ups:
- Re: Using Like operator in VBA
- From: Marshall Barton
- Re: Using Like operator in VBA
- Prev by Date: sql stored procedure
- Next by Date: Re: find files - browse harddrive for files
- Previous by thread: sql stored procedure
- Next by thread: Re: Using Like operator in VBA
- Index(es):
Relevant Pages
|
