| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Something wrong in one line of VBA code
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 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Something wrong in one line of VBA code
Try changing the * wildcard with the % wildcard.
HTH, -- Gary Brown 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 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Something wrong in one line of VBA code
Try replacing the * wildcard with the % wildcard.
HTH, -- Gary Brown 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 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Something wrong in one line of VBA code
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) -- McManCSU ------------------------------------------------------------------------ McManCSU's Profile: http://www.excelforum.com/member.php...o&userid=24379 View this thread: http://www.excelforum.com/showthread...hreadid=382627 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Something wrong in one line of VBA code
You're a genius!! Thanks so much.
"Gary L Brown" wrote: Try replacing the * wildcard with the % wildcard. HTH, -- Gary Brown 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 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Combobox has no arrow, just a line on the right, what's wrong? | Excel Worksheet Functions | |||
| Macro code to put series name next to individual line in line grap | Charts and Charting in Excel | |||
| What is wrong with this line? | Charts and Charting in Excel | |||
| Whats wrong with this line of code? | Excel Programming | |||
| What's wrong with this line of code? | Excel Programming | |||
Hybrid Mode
