looking for a match comparing field to value
excel 2003
access 2003
vba
is it possible to turn this code into a "Like" wildcard match ?
' This works
If CStr(rng.Offset(0, -1).Value) = rs.Fields(1) Then
.Range("A" & j).Offset(0, 52) = "Found"
End If
This does not work:
If CStr(rng.Offset(0, -1).Value) Like "*' & rs.Fields(1) & '*" Then
.Range("A" & j).Offset(0, 52) = "FoundSomething"
End If
Thanks
fordraiders
access 2003
vba
is it possible to turn this code into a "Like" wildcard match ?
' This works
If CStr(rng.Offset(0, -1).Value) = rs.Fields(1) Then
.Range("A" & j).Offset(0, 52) = "Found"
End If
This does not work:
If CStr(rng.Offset(0, -1).Value) Like "*' & rs.Fields(1) & '*" Then
.Range("A" & j).Offset(0, 52) = "FoundSomething"
End If
Thanks
fordraiders
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Is there a syntax error with your single quote? Shouldn't it be:
If CStr(rng.Offset(0, -1).Value) Like "*" & rs.Fields(1) & "*" Then
It should work just fine with that corrected.
If CStr(rng.Offset(0, -1).Value) Like "*" & rs.Fields(1) & "*" Then
It should work just fine with that corrected.
Ok then, when you want a wildcard match, don't give up - just ensure the syntax is correct ;)
Dave
Dave
ASKER
one quirk
rng.Offset(0, -1).Value = "PROTO"
YOUR CODE will not find "PROTO" if its at the end of the string ?
If Instr(rng.Offset(0, -1).Value, rs.Fields(1)) > 0
if the field rs.fields(1) = "BLACKHAWK BY PROTO"
rng.Offset(0, -1).Value = "PROTO"
YOUR CODE will not find "PROTO" if its at the end of the string ?
If Instr(rng.Offset(0, -1).Value, rs.Fields(1)) > 0
if the field rs.fields(1) = "BLACKHAWK BY PROTO"
ASKER
dlmile, would not work...sorry
"BLACKHAWK BY PROTO" like "*PROTO*" works - your wildcards will work if you use the correct syntax - see my post.
Dave
Dave
fordraiders - certainly does work. Just use the LIKE properly correctly.
Here's the proper use from Excel Help:
Like Operator
Used to compare two strings.
Syntax
result = string Like pattern
Consult Excel Help for the GUI version of how LIKE works.
Its STRING like PATTERN, not PATTERN LIKE STRING.
Cheers,
Dave
Here's the proper use from Excel Help:
Like Operator
Used to compare two strings.
Syntax
result = string Like pattern
Consult Excel Help for the GUI version of how LIKE works.
Its STRING like PATTERN, not PATTERN LIKE STRING.
Cheers,
Dave
ASKER
dave,
If CStr(rng.Offset(0, -1).Value) Like "*" & rs.Fields(1) & "*" Then
.Range("A" & j).Offset(0, 53) = "NAME MATCH"
End If
it will not find "PROTO" CStr(rng.Offset(0, -1).Value) inside "BLACKHAWK BY PROTO" "*" & rs.Fields(1) & "*"
If CStr(rng.Offset(0, -1).Value) Like "*" & rs.Fields(1) & "*" Then
.Range("A" & j).Offset(0, 53) = "NAME MATCH"
End If
it will not find "PROTO" CStr(rng.Offset(0, -1).Value) inside "BLACKHAWK BY PROTO" "*" & rs.Fields(1) & "*"
You're doing it incorrectly.
If "PROTO" is in rng.offset(0,-1).value
and "BLACKHAWK BY PROTO" is in rs.Fields(1)
Then the correct syntax is STRING LIKE PATTERN
If rs.Fields(1) LIKE "*" & CStr(rng.Offset(0,-1).Valu
If "PROTO" is in rng.offset(0,-1).value
and "BLACKHAWK BY PROTO" is in rs.Fields(1)
Then the correct syntax is STRING LIKE PATTERN
If rs.Fields(1) LIKE "*" & CStr(rng.Offset(0,-1).Valu
ASKER
dave. Thanks for staying with me...ok I see what you mean now...
ok now how do you get some points ?
ok now how do you get some points ?
No need.
by the way - you might be doing the INSTR function backwards as well, since you noted it didn't work on another example, unless I missread the post - just make sure the STRING is the first parameter.
Also, suggest waiting a few :) minutes before accepting solutions especially if they come from a tangent (re: you were given a perfectly good solution, however, it didn't explain why you were having a problem with LIKE), as several Experts might be banging away competing solutions and you can have your pick, reward the first, or reward them all.
Cheers,
Dave
by the way - you might be doing the INSTR function backwards as well, since you noted it didn't work on another example, unless I missread the post - just make sure the STRING is the first parameter.
Also, suggest waiting a few :) minutes before accepting solutions especially if they come from a tangent (re: you were given a perfectly good solution, however, it didn't explain why you were having a problem with LIKE), as several Experts might be banging away competing solutions and you can have your pick, reward the first, or reward them all.
Cheers,
Dave
ASKER
Thanks, again...very much ! and yes I will wait next time'
ASKER