Return a value

[ecugrad]

I have the below SQL Statement behind my form. I want my txtAugust field to show 0 if I don't have any entries in the table for the selected employee. I will use the zero for other equations. My problem is if there is not an entry I'm can't get a zero returned..

strSQL = "SELECT Sum(G1+G2+P1+P2+P3+P4+P5+P6+P7+TC1+TC2+TC3+TC4+TC5+TC6+TC7+C1+C2+C3+C4+K1+K2)AS newCallTotals FROM tblNewCallMon "
strSQL = strSQL & "WHERE ((tblNewCallMon.EmployeeID)= " & EmployeeID & &quotAND ((tblNewCallMon.CallDate) Between #08/01/02# AND #08/31/02#) ;"
Set rs = Db.OpenRecordset(strSQL, dbOpenSnapshot)

If rs!newCallTotals = "" Or IsEmpty(rs!newCallTotals) Or (rs!newCallTotals) = 0 Then
rs!CallTotals = "0"
Else

frm!txtAugust = rs!newCallTotals
rs.Close
strSQL = "'"

End If

Thanks,

Mike

 

[FancyPrairie]

Check the eof property of the recordset. If true, no records returned. Therefore, set the value to 0.

Set rs = Db.OpenRecordset(strSQL, dbOpenSnapshot)

if (rst.eof) then
txtAugust = 0
else
blah...blah...blah
end if