using dmax and date field

[Guest]

Hi,

I have a table with data like this

date runnumber
1/1/2007 1
1/1/2007 2
1/1/2007 3

I am using dmax to get the maximum runnumber value where date is 1/1/2007
'getrunnumber = Nz(DMax("[UploadRun]", "CRSFILE_HISTORY", "[DateCreated] =
#" & CDate(sdateshort) & "#"), 0) + 1

the problem is is that it always evaluates to 0 or null because it is
looking at the time to exact second as well.

I would like to know how to programmatically get only the date into the
field so I don't have this problem. If I manually enter the date into the
access table then the time (hh:mm:ss) do not getting added.

or

how do I get dmax to only evaluate the date portion of the field and not the
time portion as well.

Thanks

Chris

 

[Guest]

Try using the DateValue to remove the time

getrunnumber = Nz(DMax("[UploadRun]", "CRSFILE_HISTORY", "DateValue
([DateCreated]) = #" & DateValue(CDate(sdateshort)) & "#"), 0) + 1

 

[John W. Vinson]

I would like to know how to programmatically get only the date into the
field so I don't have this problem. If I manually enter the date into the
access table then the time (hh:mm:ss) do not getting added.

It sounds like you may have the (badly named, Date is a reserved word) field
defaulting to Now() or being set to Now(). the Now() function does not return
the date; it returns the date and time accurate to a few microseconds! Use
Date() instead, if the date is what you want stored.

John W. Vinson [MVP]