The attached code/function worked fine in when the database was Access. After upsizing from Access to SQL Server, I'm getting an unexpected result, which is this: If the sql statement finds no record of a given orderID, it still returns a recordset. I have found that although the sql requests only a single field (totalPayment), the Recordset returned contains two fields (totalPayment, orderID). So in SQL Server, even if no match is found for a given orderID, a Recordset is returned where EOF = false, RS.Fields(0).value = NULL, and RS.Fields(1).value = <orderID>.
Is there a way to alter this behavior by SQL Server?
function getTotalPayment(byval orderID)
getTotalPayment = 0
dim sql, RS
sql="select totalPayment from qryTotalCostAndPayment where orderID=" & orderID
' getRS uses existing db connection to retrieve RS
set RS = getRS(sql, adLockOptimistic)
if not RS.EOF then
getTotalPayment = RS.Fields("totalPayment").value