Error when using variable when connecting to SQL from Excel

[JKwan]

I think you are missing the single quote, give this a shot:

With StockIssuesData
 .ActiveConnection = StockIssuesConn
 .Source = "SELECT Year, Period, BatchRef, TransRef, LedgerCode, Contract, Description, Stockno, Quantity, Unit, Rate, Debit, Credit, Store, SYSDATE FROM TRANSACTIONS WHERE  (Store = '" & StorNum & "') AND (TransType = 'STB') AND (Allocation = 'Contracts')ORDER BY TransRef, Stockno"
 .LockType = adLockReadOnly
 .CursorType = adOpenForwardOnly
 .Open
End With