Dmin for nulls?

[RogerH]

I have a table of Transactions "tblTransactions" in which more than one row will have the same PO number. Each row has a date field called "POSubDate" I need to know if *any* row has a null in that field.

I tried:

If IsNull(DMin("POSubDate", "tblTransactions", "POno = " & Chr(34) & Me.PONo & Chr(34))) Then ...

But: DMin() returns Null if they are ALL null. But if ANY have a date, it returns the earliest date.

I need a DNull() function!

 

[pbaldy]

My first thought:

If DCount("*", "tblTransactions", "POno = " & Chr(34) & Me.PONo & Chr(34) & " AND POSubDate Is Null) > 0 Then

 

[The_Doc_Man]

Write a query similar to this:

Select A, B, etc., IsNull(X) As XNull From ....

Then do a DCount("[XNull]", "myquery", "[XNull]=True" )

 

[pbaldy]

Doc, you're saying my DCount() won't work? I didn't test, but I don't see why it wouldn't work.

 

[The_Doc_Man]

Giving the OP a second viewpoint, not meant to downplay your solution. However, I recall from some years a go a discussion with someone about how DCount and a few of the other Domain Aggregate functions have a hard time with nulls. No, I just saw this post and haven't had time to try it. I wasn't saying it would or wouldn't work. Just offering another viewpoint. You should know by now that I do that because it always helps the newcomers to see other ways to skin cats....

 

[pbaldy]

Either your extra query or the DCount would require a criteria on POno.

 

[RogerH]

Thanks guys,
OK a query with an Nz([POSUbDate])
and then count the zeros.

Although, now I'm thinking I *could* write a DNull() function that works via ADO, it would probably be slower..

 

[Cassi]

If DCount("*", "tblTransactions", "POno = " & Chr(34) & Me.PONo & Chr(34) & " AND POSubDate IS NULL") > 0 Then
    ' At least one row has a null value in "POSubDate"
    ' Your code here...
Else
    ' No row has a null value in "POSubDate"
    ' Your code here...
End If

Is this code using DCount() usable?

 

[Josef P.]

If you only want to check if there is at least one record with zero, DLookup would also suffice.

Auxiliary function:

Public Function DExists(ByVal Domain As String, _
               Optional ByVal Criteria As String = vbNullString) As Boolean

   DExists = Nz(DLookup("True", Domain, Criteria), False)

End Function

Usage:

Dim CriteriaString as String
CriteriaString = "POno = '" Me.PONo & "' AND POSubDate Is Null"
if DExists("tblTransactions", CriteriaString) then
...

See also:

Speed Comparison Tests 7

This article discusses 7 different methods for checking if a record exists. It is the seventh in a series of articles discussing various tests done to compare the efficiency of different approaches to coding. Example databases are provided so the tests can be done on your own workstations

isladogs.co.uk