FindFirst, NoMatch problem

[Andrew Smith]

This is driving me mad. I have the following code that is meant to
check if a date field in any record of a recordset contains a null
value:

Start of code.....
With rst
..MoveFirst
Do Until .EOF
Debug.Print !dateend,
Debug.Print IsNull(!dateend)
..MoveNext
Loop
..MoveFirst

..FindFirst IsNull(!dateend)
If .NoMatch Then
More code here .....
..... End of code

I added the loop through the recordset and the debug statements as the
NoMatch was returning True when I was sure it should be false. The
output from the debug.print statements was:

26/01/2005 False
26/01/2005 False
26/01/2005 False
Null True
26/01/2005 False

So, there is one record with a null value in the dateend field, but the
NoMatch property still came up as True.

I'm going to change the code so that I loop through the recordset to
find the null values, but I'd still like to know why it doesn't work.

 

[Jeff Boyce]

Andrew

Just an observation...

Iterating through rows via code is a compute-intensive way to find a null.
It would be much faster to use a query...

Jeff Boyce
<Access MVP>

 

[Andi Mayer]

This is driving me mad. I have the following code that is meant to
check if a date field in any record of a recordset contains a null
value:

Start of code.....
With rst
.MoveFirst
Do Until .EOF
Debug.Print !dateend,
Debug.Print IsNull(!dateend)
.MoveNext
Loop
.MoveFirst

.FindFirst IsNull(!dateend)
If .NoMatch Then
More code here .....
.... End of code

out of the help file:

criteria
A String used to locate the record. It is like theWHERE clause in an
SQL statement, but without the word WHERE.

therefore:
..FindFirst "IsNull(dateend)"

but still Jeffs suggestion is valid

 

[Tim Ferguson]

A String used to locate the record. It is like theWHERE clause in an
SQL statement, but without the word WHERE.

therefore:
.FindFirst "IsNull(dateend)"

I think you mean

.FindFirst "DateEnd IS NULL"

which is what the SQL criterion should be. Of course, as you say, it
should be in the actual SQL, not here:

strSQL = "SELECT COUNT(*) AS NumRec FROM Somewhere WHERE DateEnd IS
NULL"
dwCount = db.OpenRecordset( _
strSQL, dbOpenSnapshot, dbForwardOnly)!NumRec

B Wishes


Tim F

 

[Andi Mayer]

I think you mean

.FindFirst "DateEnd IS NULL"

No
isnull(fieldname) works also
yours is more SQL like

 

[Andrew Smith]

Many thanks - either "IsNull(DateEnd)", or "DateEnd Is Null" work. I'd just
missed the fact that I needed a string.

I realise that a query is faster than iterating through a recordset, but
I've got this recordset open for other reasons anyway and it won't ever
contain more than about 10 records so it seemed the easiest way to get the
job done.

 

[Andrew Smith]

Jeff,

Thanks for the reply. I've got it working now thanks to the other replies,
but I was just interested in how much difference in time it would make to do
this in various different ways. I've written four functions that will return
a boolean value to indicate whether there is a null value in the date field:

1 - open a recordset and use the FindFirst method followed by the .NoMatch
propert
2 - open a recordset based on query, then look at the .RecordCount property
3 - use a DCount function
4 - open a recordset and iterate through all records until a null is found

The times taken to run each function 10,000 times on a 42 record recordset
were:

1 35s
2 36s
3 34s
4 37s

So, for one iteration the time difference between the fastest and slowest is
0.3 milliseconds