need help with max query

[Chris]

Hi,

Here is my code:

ddate = 4/1/2007

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT max(UploadRun)as [MaxRun] FROM
CRSFILE_HISTORY where DateCreated =#" & dDate & "#")

If rst.EOF = True And rst.BOF = True Then
GetRunNumber = 1
End If

rst.MoveLast
rst.MoveFirst

If rst.RecordCount = 0 Then
GetRunNumber = 1
rst.Close
Set rst = Nothing
Exit Function
End If

EOf and BOF are not evaluating to true
recordcount gives me 1.

This table is completely empty.

When I change the select to : select * from crsfile_history, the
recordcount evaluates to 1.

If I put a record into the table that will give a record back it does
provide a record and I can read the values (with additional code). So I don't
think the syntax is wrong.

Please advise.

Chris

 

[Klatuu]

It wll have one record with one field named MaxRun. It will have the value
Null.

If all you want is that one value, the DMax function would be better:
ddate = #4/1/2007#
MaxRun = Nz(DMax("[UploadRun]", "CRSFILE_HISTORY", "[DateCreated] = #" &
ddate & "#"),0)

The Nz function is there so if there is no match, it will retun 0 instead of
Null.

 

[Jason Lepack]

It is returning a single null record.

Because it will always return a single record, then there will either
be a value or null, so use this validation:

if isnull(rst.fields("MaxRun").value) then
GetRunNumber = 1
rst.Close
Set rst = Nothing
Exit Function
end if