Something wrong in one line of VBA code

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

Try ...
src = src & "HAVING (((Master_Deal.Parent) Like " & chr(34) & "*" &
myCompany & "*" & chr(34)));"
I haven't tried this.
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?".


"Daniel Bonallack" wrote:

> 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
>
>
>

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


"Gary L Brown" wrote:

> Try ...
> src = src & "HAVING (((Master_Deal.Parent) Like " & chr(34) & "*" &
> myCompany & "*" & chr(34)));"
> I haven't tried this.
> 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?".
>
>
> "Daniel Bonallack" wrote:
>
> > 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
> >
> >
> >

Try changing 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?".


"Daniel Bonallack" wrote:

> 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
>
>
> "Gary L Brown" wrote:
>
> > Try ...
> > src = src & "HAVING (((Master_Deal.Parent) Like " & chr(34) & "*" &
> > myCompany & "*" & chr(34)));"
> > I haven't tried this.
> > 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?".
> >
> >
> > "Daniel Bonallack" wrote:
> >
> > > 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
> > >
> > >
> > >

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?".


"Daniel Bonallack" wrote:

> 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
>
>
> "Gary L Brown" wrote:
>
> > Try ...
> > src = src & "HAVING (((Master_Deal.Parent) Like " & chr(34) & "*" &
> > myCompany & "*" & chr(34)));"
> > I haven't tried this.
> > 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?".
> >
> >
> > "Daniel Bonallack" wrote:
> >
> > > 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
> > >
> > >
> > >

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)

You're a genius!! Thanks so much.

"Gary L Brown" wrote:

> 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?".
>
>
> "Daniel Bonallack" wrote:
>
> > 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
> >
> >
> > "Gary L Brown" wrote:
> >
> > > Try ...
> > > src = src & "HAVING (((Master_Deal.Parent) Like " & chr(34) & "*" &
> > > myCompany & "*" & chr(34)));"
> > > I haven't tried this.
> > > 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?".
> > >
> > >
> > > "Daniel Bonallack" wrote:
> > >
> > > > 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
> > > >
> > > >
> > > >