SQL wildcard not working in VBA

pook_666

Board Regular
Joined
Aug 16, 2018
Messages
80
Hi team,

I have an issue with the below SQL code I am using in VBA where the last line to find the wildcard of everything beginning with 0LC isn't coming up with results when I run the VBA.

When I run it in SQL Server Management it works and if in VBA I remove the last line, I can see results and there are cells beginning with 0LC so my only guess is something is wrong with the characters I am putting around the 0LC% within the VBA code?

VBA Code:
    SQLQry = " select ST_ALOC_POINTER as 'Customer Code', ST_TRANTYPE as 'Transaction Type', ST_DESCRIPTION as 'Description', ST_GROSS as 'Local Amount', ST_CURRENCYCODE as 'Foreign Currency', ST_CURR_VALU as 'Foreign Currency Amount', ST_PERIODNUMBER as 'Period', ST_YEAR as 'Year'" & _
             " from dbo.SL_TRANSACTIONS" & _
             " where ST_YEAR = '" & Year & "'" & _
             " and ST_PERIODNUMBER = '" & Period & "'" & _
             " and ST_ALOC_POINTER like '0LC%'"
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
592
Office Version
  1. 365
Platform
  1. Windows
I have printed your SQL string to the debug.print window and it looks fine to me.
SQL:
select ST_ALOC_POINTER as 'Customer Code', ST_TRANTYPE as 'Transaction Type', ST_DESCRIPTION as 'Description', ST_GROSS as 'Local Amount', ST_CURRENCYCODE as 'Foreign Currency', ST_CURR_VALU as 'Foreign Currency Amount', ST_PERIODNUMBER as 'Period', ST_YEAR as 'Year' from dbo.SL_TRANSACTIONS where ST_YEAR = '2020' and ST_PERIODNUMBER = '10' and ST_ALOC_POINTER like '0LC%'
If its not erroring out and just returning zero records, that would indicate that the overall syntax is fine.
Can you copy to above into the SQL query window as it is and see if it works. If its still give zero records, is there any chance the 0 should be O ?
 
Solution

pook_666

Board Regular
Joined
Aug 16, 2018
Messages
80
I'm not sure what happened but when I ran it this morning it worked.....probably some Excel update issue or something!

Thanks Alex.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,899
Messages
5,638,891
Members
417,058
Latest member
BRYCEPIETROWIAK

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top