Date Function

K

Kim

I have a function that returns the date of a record. If the date is null
however, it returns 12:00:00 AM. If the date doesn't exist, I don't want to
see anything. Ideas?


TIA,

Kim
 
J

John Vinson

Kim said:
I have a function that returns the date of a record. If the date is null
however, it returns 12:00:00 AM. If the date doesn't exist, I don't want to
see anything. Ideas?
Click to expand...

So... what's the function???

A Date/Time value is stored as a number, a count of days and fractions of a
day (times) since midnight, December 30, 1899. As such, a 0 value is
equivalent to #12/30/1899 12:00:00am#, midnight on that long-ago day.

You don't really need a function if your record contains a date/time
timestamp; just set the format of the field to display the date portion. It
appears that you're using the NZ() function and getting a zero... right?

John W. Vinson/MVP
 
R

Rick B

You would probably need to share your function with us if you want us to
help you modify it to do something different.
 
K

Kim

This is my code. I am using this function to in a datediff calculation.


Public Function GatherKeyDates(ByVal sRDNumber, ByVal strField, ByVal
lKeyDateID) As Date
On Error GoTo Err_GatherKeyDates

Dim dtResult As Date, sSQL As String
Dim MyRS As ADODB.Recordset
Dim sResult As String

sSQL = "Select tbl_RD_KeyDates." & strField
sSQL = sSQL & " FROM tbl_RD_KeyDates "

sSQL = sSQL & "WHERE (((tbl_RD_KeyDates.RD_Number)= '" & sRDNumber & "')
AND ((tbl_RD_KeyDates.KeyDateTypeID)= " & lKeyDateID & " ) AND
((tbl_RD_KeyDates." & strField & " ) Is Not Null));"

Set MyRS = New ADODB.Recordset

With MyRS
.CursorLocation = adUseClient
.Open Source:=sSQL, ActiveConnection:=CurrentProject.Connection
If .RecordCount Then
dtResult = Nz(.Fields(strField).Value, 0)
End If
.Close
End With

Exit_GatherKeyDates:
Set MyRS = Nothing
GatherKeyDates = dtResult

Exit Function

Err_GatherKeyDates:
Call ErrHandler("GatherKeyDates function", Err.Number, Err.Description)
Resume Exit_GatherKeyDates

End Function
 
J

John Vinson

You'll need to specify the datatype of the function as a Variant, and Dim
dtResult as a Variant; remove the NZ() function calls when assigning a value
to dtResult as well.

A Date datatype cannot contain a NULL value (or a zero length string for
that matter), only a real date.

John W. Vinson/MVP
 
Top