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

The smart man learns from his mistakes, but the wise man learns from the mistakes of others.
The sock monster got your code
Go to the top of the page
tina t
post Aug 10 2018, 11:57 AM

Posts: 5,483
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.


"the wheel never stops turning"
Go to the top of the page
John Vinson
post Aug 10 2018, 08:19 PM

UtterAccess VIP
Posts: 4,258
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!

John W. Vinson
Wysard of Information
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    20th August 2018 - 06:56 PM