Hi Sarah,
Please remember to put your name at the end of
your message. It helps us.
I can't help with the query itself. My brain switches
off after about 150 characters characters of SQL.
To understand the NZ Function, here are some examples
to get a feel for what the NZ function does.
Copy the list to the Debug Window and execute
each line in turn.
a="fred"
?NZ(a,"a is Null")
?NZ(a,"0")
?NZ(a,-1)
a=""
?NZ(a,"a is Null")
?NZ(a,"0")
?NZ(a,-1)
a=null
?NZ(a,"a is Null")
?NZ(a,"0")
?NZ(a,-1)
So basically if 'a' is Null, you get the default value
otherwise you get the value of the variable, 'a' in
the above cases
Hope that helps.
Regards, Clive.
--- In MS_Access_Professionals@yahoogroups.com, "novice012000" wrote:
>
> 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
>
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (5) |
Tidak ada komentar:
Posting Komentar