Sarah
If the field allows zero-length strings you can test for blanks this way:
WHERE Len([inrcd] & "")=0
That will pick up all ZLS and Nulls.
Regards,
Bill Mosca, Founder - MS_Access_Professionals
http://www.thatlldoit.com
Microsoft Office Access MVP
https://mvp.support.microsoft.com/profile=C4D9F5E7-BB03-4291-B816-64270730881E
My nothing-to-do-with-Access blog
http://wrmosca.wordpress.com
--- 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 (6) |
Tidak ada komentar:
Posting Komentar