ACCESS FEED: [MS_AccessPros] Help with query

Senin, 18 Februari 2013

[MS_AccessPros] Help with query

I am having a problem with the following query. I am trying to count all records where the 'insrcd' is not filled in. When 'insfrom' is blank and 'insto' is blank it will not find the records where the 'insrcd' is blank. If 'insfrom' is blank and 'insto' has a value, the query works fine, but I also need to be able to find all records where the 'insrcd' is not filled in. I enter the range of 'insrcd' codes I want to count in the form 'zfomr1b' (I am having a problem understanding the NZ( function).
As always all help is greatly appreciated.
Sarah
Private Sub GroupFooter0_Format(cancel As Integer, FormatCount As Integer)
Dim sqlstr As String, sday As String, pmb As String, ltemp As Long, insfrom As String, insto As String
Dim rstsql As DAO.Recordset
Set dbsmed1 = CurrentDb()
sday = dw1(Me![shift date])
pmb = Forms![zform1b]![pmb]
insfrom = Nz(Forms![zform1b]![insfrom], "")
insto = Nz(Forms![zform1b]![insto], "")
If pmb = "*" Then pmb = "M' or 'P"

sqlstr = "SELECT Count(Patient." & sday & ") AS [count]" & _
" FROM Patient" & _
" WHERE (nz(Patient.[insrcd],'""') Between '" & insfrom & "' And '" & insto & "') AND ((((Patient.[end date]) > #" & Me![shift date] & "#)) Or (((Patient.[end date]) Is Null) AND ((Patient.[start date])<=#" & Me![shift date] & "#))) " & _
" GROUP BY Patient.[preferred shift], Patient." & sday & ", Patient.[billing type]" & _
" HAVING (((Patient.[preferred shift])=" & Me![shift #] & ") AND ((Patient." & sday & ")=" & True & ") AND ((Patient.[billing type])='" & pmb & "'));"

Set rstsql = dbsmed1.OpenRecordset(sqlstr, dbOpenDynaset, dbReadOnly)
On Error GoTo end1:
ltemp = rstsql![count]
'Stop
rstsql.MoveNext
If rstsql.EOF = False Then
ltemp = ltemp + rstsql![count]
End If
Me![schfordate].Caption = ltemp
end1:
End Sub