Something wrong in one line of VBA code

[Guest]

I copied a procedure from a Walkenbach module that pulls data from Access and
puts it into Excel - it works fine.

But I wanted to adapt it so that pulls a specific company that the user
enters. As they may not enter the exact correct name, I wanted to use the
"Like" facility.

This is the piece I copied from the final line of SQL code I took from Access
HAVING (((Master_Deal.Parent) Like "*Exxon*"));

And I adapted it to Excel VBA form with a variable:
src = src & "HAVING (((Master_Deal.Parent) Like '*" & myCompany & "*'));"

In Access I get give lines returned, in Excel, nothing. Am I correctly
using the LIKE function in Excel VBA?

Hope this is clear, and thanks for any help.

Daniel

 

[Guest]

Try ...
src = src & "HAVING (((Master_Deal.Parent) Like " & chr(34) & "*" &
myCompany & "*" & chr(34)));"
I haven't tried this.
HTH,

 

[Guest]

Thanks Gary, but unfortunately not - it still returns zero records.
I haven't had problems with the apostrophe itself, for example if I put the
exact name in:
src = src & "HAVING (((Master_Deal.Parent) = 'Exxon Ltd'));"
then it returns records.

It's the "LIKE" function that's killing me!

Thanks for replying though.

Daniel

 

[Guest]

Try changing the * wildcard with the % wildcard.
HTH,

 

[Guest]

Try replacing the * wildcard with the % wildcard.
HTH,

 

[McManCSU]

You could also use the built in function:

StrConv(<string name>, <number>) Where this would take care of
lower/uppercase problems. For number, 1 is lower, 2 upper, 3 'proper'
(joe blow becomes Joe Blow)

 

[Guest]

You're a genius!! Thanks so much.

Try replacing the * wildcard with the % wildcard.
HTH,
--
Gary Brown
gary_brown@ge_NOSPAM.com
If this post was helpful, please click the ''''''''Yes'''''''' button next
to ''''''''''''''''Was this Post Helpfull to you?".