date function return value issue

[live2ride]

ok this is little bit frustrating. can someone please explain this issue

Function fMod() As Date
fMod= Nz(DLookup("DateModified", "tblUsers", "UserID = " & fUserID))
End Function

msgbox fMod (value = '12:00:00 AM') - wrong value
msgbox Nz(DLookup("DateModified", "tblUsers", "UserID = " & fUserID)) (value = '10/24/2012 5:15:00 PM) - correct value

[ssanfu]

I've seen something like that before. So I always define a variable first.
what is the result if you try this?

Function fMod() As Date
  Dim tmp as Date

  tmp= Nz(DLookup("DateModified", "tblUsers", "UserID = " & fUserID))
  
  fMod= tmp
End Function

[live2ride]

thats working fine, i always try to use the least amount of code as possible so thats why sometimes im trying to figure out these little issues which are irritating
im constantly switching between access 2007 and 2010 and im having terrible time, while erything is working fine in one version other version fails all the time

[June7]

You get 12:00:00 AM when you know DateModified field has 10/24/2012 5:15:00 PM or is the midnight time returned only for records where the DateModified field is Null?

You use Nz function but don't specify a value to use if field is Null. I tested Nz in a query on various datatype fields and in VBA Immediate window. If no value specified, the result appears to be an empty string.

The function is declared as Date but an empty string is not a date so maybe this is causing issue.