X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Datevalue(0) Returns Time - Solved, Access 2016    
post Aug 10 2018, 09:15 AM

Posts: 178
Joined: 3-July 06
From: New York, NY

Note - one of the best things I get from UA is just writing my question. In my efforts to write a good question, I often figure out the answer. Others might have that same experience. But I also feel bad I don't contribute more. I'm not the guru many here are. But in case it helps someone (searching 'date' '0' etc was hard to narrow down, in case I missed it elsewhere). Anyway, I thought I'd post anyway in case someone had other thoughts or needed the answer I just found. Is that ok?

I'm trying to clean up some date-time fields. The fields right have not date or time or neither or whatever... it's a mess.
All ok though, but when I do the equivalent of datevalue(0), I'm seeing timevalue(0), or 12:00:00 AM.

In the screenshot example, you see in the 5th line Call Disp = 12/30/1899 11:58:58 PM. Essentially that's a DateValue of 0. So in the dd column, I think I should see 12/31/1899. But instead, I see a time value of 0, or 12:00:00 AM

dd: Nz(DateValue([Call Disp]),DateValue([RecdDT]))

The field is not formatted in query properties. it actually doesn't have a format option because Nz is the outermost formula. I tried adding an outer DateValue function. That gave me formatting options, but the result was the same.
Then I formatted as General Date, again, no luck, still Time only.
Then I formatted as Short Date, and eureka. I got the 12/31/1899 I was looking for. I thought it was there, but it was odd to me it never showed up... Anyway, solved, but it took a rigorous question to solve the mystery.

Bonus: I thought the nz had to be applied to the datevalue in case there was no date on a time value. But it seems to read as 0 ok, so this simpler version works.
dd: DateValue(Nz([call disp],[recddt]))
This post has been edited by dday76: Aug 10 2018, 09:22 AM
Attached File(s)
Attached File  dt.png ( 8.16K )Number of downloads: 0
Go to the top of the page
tina t
post Aug 10 2018, 11:57 AM

Posts: 5,580
Joined: 11-November 10
From: SoCal, USA

I thought the nz had to be applied to the datevalue in case there was no date on a time value

fyi, any value stored as a Date/Time data type in Access includes both a date and a time.

Go to the top of the page
John Vinson
post Aug 10 2018, 08:19 PM

UtterAccess VIP
Posts: 4,273
Joined: 6-January 07
From: Parma, Idaho, US

As Tina correctly said, any and all Access Date/Time fields contain both a date (the integer portion of the Double Float numeric value) and a time (the fractional portion).

The data will DISPLAY differently depending on the context. You can set the Format of a field to (say) "mm/dd/yy" and a zero value (or an NZ() function return) will show 12/30/99; you can set the Format to "hh:nn:ss" and it will show "00:00:00"; you can set no format at all, and Access will show whatever the programmers chose to show in such a case - e.g., 12:00:00 AM. Data storage and data display are interrelated but different concepts - especially for date/time data!
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    14th November 2018 - 04:59 PM