SQL wildcard not working in VBA

[pook_666]

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?

    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%'"

 

[Alex Blakenburg]

I have printed your SQL string to the debug.print window and it looks fine to me.

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 ?

 

[pook_666]

I'm not sure what happened but when I ran it this morning it worked.....probably some Excel update issue or something!

Thanks Alex.