Force record return from SQL Download

Hi there,

Don't know whether this is the correct sub-forum for this (Please move if it isn't)

Does anyone know of a way to force (either on the VBA or SQL side) a query to return zero for results not in a query.

So say you have information for A, B, C, E in the Database, but you want to return data for A, B , C, D, E, F, is there any way to get excel to return D = 0, F = 0 in what is copied to the excel spreadsheet?

In context, from the code below, I want to return info for all of the currencies listed in the below query (but zeros if nothing exists in the database table.)

I'm not sure whether using a .find method would work necessarily, as I have to copy about 6 similar recordsets next to each other (all for currencies), and there may be info posted for a currency on one recordset, but not on another.

Thanks in advance


Here's my code:

Sub TradarBalances()
Dim Constr, sql As String
Dim Conn, itemscmd, itemsrs As Object
Const FundArrList = "Globe||Map_155||UK FNYS"
Const AccountArrList = "ML Accrual||Merril Lynch CFD"
Const StratArrList = "PB Interest Accrual||PB Stock Loan Accrual"
Dim FundArr() As String
Dim AccountArr() As String
Dim StratArr() As String
Dim CopyRange As Range
Dim Fund As Variant

Set CopyRange = Sheets("Cash Balances").Range("R5:R22")

FundArr = Split(FundArrList, "||")
AccountArr = Split(AccountArrList, "||")
StratArr = Split(StratArrList, "||")

Constr = "DRIVER={SQL Server}; DSN=sql2k5Tradar; SERVER=TradarDB\TRADARSERVER; UID=FNYSL\j.windridge; WSID=DLN-BB-MB-01; database=TradarBE; Trusted_Connection=Yes"

Set Conn = CreateObject("ADODB.Connection")
Conn.Open Constr
Set itemscmd = CreateObject("ADODB.Command")
Set itemsrs = CreateObject("ADODB.Recordset")
Set itemscmd.ActiveConnection = Conn
itemscmd.CommandTimeout = 300

For Each Fund In FundArr
sql = "SELECT SUM(moneyspot) as Value FROM [Trade] where strat in ('PB Interest Accrual') and id in ('AUD','CAD','CHF','CZK','DKK','EUR','GBP','HKD','ILS','JPY','MXN','MYR','NOK','NZD','PLN','SEK','SGD','THB','THO','TRY','USD','ZAR') and fund = '" & Fund & "' and cancel = 0 and clr = 'ML Accruals' group by fund,clr,strat, id order by id asc"
itemscmd.CommandText = sql
itemsrs.Open itemscmd, , 0, 1



Do While Not itemsrs.EOF



CopyRange.Clear
CopyRange.CopyFromRecordset itemsrs
Set CopyRange = CopyRange.Offset(0, 1)
Loop
itemsrs.Close
Next Fund

End Sub

As you might be able to see from the code, my end goal is to copy across all combinations of the contents of the three arrays next to each other in a sheet. (Which I will then use for an index & match query). I'm fairly sure of how I can do this, its just making sure that the same number of rows are returned for each query (so that which line refers to which currency stays constant)