Problems using Date formatting.

[MikeCouling]

I'm processing moth records and am intereted in the earliest & latest appearnces of each species. I'm getting a problem when formatting the date - it seems to subtract a day. I'm using the following SQL :-
SELECT
Taxa.Taxon,
Records.Date,
Format([Date],"ddmmmyyyy") AS RecordDate,
DatePart("y",[date]) AS DayOfYear,
Format(DatePart("y",[date]),"mmmdd") AS DateOfYear
FROM Records INNER JOIN Taxa ON Records.[*Taxon] = Taxa.[_guk]
WHERE (((Taxa.Taxon)="Agriopis leucophaearia") AND ((Records.Date)=[dateto]))
ORDER BY DatePart("y",[date]);

This gives the following :-

Formatting the date seems to have subtracted 1 from its value. Could this be that the date is stored as a floating point number (it's a long integer in the Records table) and there's rounding going on, or am I over-thinking things.
Suggestions gratefully received.

 

[Minty]

I thin you can simplify this

Format(DatePart("y",[date]),"mmmdd") AS DateOfYear

to

Format([date],"mmmdd") AS DateOfYear

No idea why you would need to deal with year part?

 

[MikeCouling]

I thin you can simplify this

Format(DatePart("y",[date]),"mmmdd") AS DateOfYear

to

Format([date],"mmmdd") AS DateOfYear

No idea why you would need to deal with year part?

Hi Minty, thanks for the reply.
I want to find the earliest and latest dates within each year that the moths have ever appeared. So is the earliest ever March 1st or Feb 28th for example? Similarly for latest. I can then see if records reported this year are exceptional or not.
Format just changes the appearance so although I wouldn't see the year anymore, it would still be in there and the records wouldn't sort in the way that I want.

 

[The_Doc_Man]

Could this be that the date is stored as a floating point number

Dates ARE floating-point numbers. They are what is called a "TYPECAST" of DOUBLE. That TYPECAST means, in practical terms, they are stored one way but interpreted in another way. Just like Boolean is actually a BYTE integer stored as either 0 or -1 even though we see it as FALSE or TRUE. However, there is adequate precision in DOUBLE for any date and time you will see in your lifetime unless your name is Methuselah. So I didn't think that was your problem.

I did a quickie experiment in the VBA Immediate Window and got this:

debug.Print format( 43466, "y" )
1
debug.Print format( 43466, "mmmdd" )
Jan01
debug.Print format( 43466, "ddmmmyyyy" )
01Jan2019
debug.Print datepart("y", 43466 )
 1
debug.Print format( datepart( "y", 43466 ), "mmmdd" )
Dec31

In particular, note that the last step gives the same oddity you reported, and I used integers throughout. So I'm thinking there is a bit of a flaw in the logic of what you did, and after a moment of reflection, I have an answer. Just so you could see where I'm coming from, I got a link for you. In this article, there is a significant sentence.

Store, calculate, and compare Date/Time data - Microsoft 365 Apps

Provides information on how to store, calculate, and compare Date/Time data in Access and discusses why you may get unexpected results when you calculate dates and times or compare dates and times.

docs.microsoft.com

A date value of 0 represents December 30, 1899.

In your nested call, you have the year in the DatePart (i.e. 2019 is implied by 43466). But the result of that DatePart function is 1 (see second-to-last line of my experiment) and you feed that 1 into another date-formatter routine. According to the Microsoft article, a date value of 1 is December 31, 1899. So what happened is that you formatted the wrong date with that nesting of format(datepart()). You obfuscated the real date by taking a function of a function.

Minty's suggestion will fix your problem by removing the obfuscating layered function.

 

[CJ_London]

it seems to subtract a day

I think you will find it is because of day zero. Dates are stored as numbers (today is 43875) which is the number of days since 31/12/1899 (which is day 1 (ergo day 0 is 30/12/1899) - so 1/1/1900 is 2, not 1.

Format just changes the appearance

the format property does, the format function does not - it converts it to a string - and with strings, Apr01 will come before Mar31.

I suspect what you actually need is a number - mmdd so 331 (first zero not required) and 401 which you can get from the formula

note that date is a reserved word (it is a function that returns today), using it as a field name can cause unexpected results and errors

 

[MikeCouling]

That's what I get for assuming Access Day 1 was a Jan 1st !!
Thanks to Minty for the correct code - does just what I want and has cleared up my misunderstanding of Format - as has CJ's post.
Thanks Doc_Man for the link explaining Day 0 was a Dec 30th - I expect there is a good reason for this (?) and for the detailed explanation.
I take the point about using "Date" as a field name - not my choice unfortunately.

 

[theDBguy]

That's what I get for assuming Access Day 1 was a Jan 1st !!
Thanks to Minty for the correct code - does just what I want and has cleared up my misunderstanding of Format - as has CJ's post.
Thanks Doc_Man for the link explaining Day 0 was a Dec 30th - I expect there is a good reason for this (?) and for the detailed explanation.
I take the point about using "Date" as a field name - not my choice unfortunately.

Hi Mike. Glad to hear you got it sorted out. I was a little late to the party and was doing a little experiment like what Doc did. The only additional thing I tried was use the CDate() function to actually see what Access sees. (see attached image below). I forgot about the Access epoch date, so I'm glad Doc reminded us of it. Good luck with your project!

 

[MikeCouling]

Hi Mike. Glad to hear you got it sorted out. I was a little late to the party and was doing a little experiment like what Doc did. The only additional thing I tried was use the CDate() function to actually see what Access sees. (see attached image below). I forgot about the Access epoch date, so I'm glad Doc reminded us of it. Good luck with your project!

View attachment 79118

Glad I wasn't being a total wally DBguy. Dates are always a minefield whatever the platform. I got to find out about the Immediate window as well!!

 

[isladogs]

Just for info, the Excel and Access teams clearly didn't talk to each other when developing the two programs.
Although day zero in Access in 30 Dec 1899, in Excel its 31 Dec 1899. Sigh!
Both are internally consistent and I've never yet seen an issue exchanging date data between the two programs...though other members may tell you differently.

 

[moke123]

another just for info, Day 0 of a month gives you the last date of the previous month
?dateserial(2020,5,0)
4/30/2020

 

[The_Doc_Man]

I expect there is a good reason for this (?)

Microsoft has a good reason? <pffft><choke><chuckle>

I know why UNIX has a date in 1970 as its epoch date - has to do with the year of the first UNIX.

I know why OpenVMS has 17-Nov-1858 as its epoch date - has to do with Smithsonian time after a widely observed astronomical event.

Here is a little reference to advise you of why certain other dates were chosen.

Just dating: The stories behind 12 computer system reference dates

There are reasons - sometimes good reasons - that all of those seemingly random dates were chosen for epochs

www.itworld.com

The one which explains why Dec. 31, 1899 was chosen will amuse you. However, we have been calling it the wrong date all this time. According to the article, that is REALLY Jan 0, 1900. (No kidding - read the article. It's a 13-slide slideshow.)

 

[MikeCouling]

Microsoft has a good reason? <pffft><choke><chuckle>

I know why UNIX has a date in 1970 as its epoch date - has to do with the year of the first UNIX.

I know why OpenVMS has 17-Nov-1858 as its epoch date - has to do with Smithsonian time after a widely observed astronomical event.

Here is a little reference to advise you of why certain other dates were chosen.

Just dating: The stories behind 12 computer system reference dates

There are reasons - sometimes good reasons - that all of those seemingly random dates were chosen for epochs

www.itworld.com

The one which explains why Dec. 31, 1899 was chose will amuse you. However, we have been calling it the wrong date all this time. According to the article, that is REALLY Jan 0, 1900. (No kidding - read the article. It's a 13-slide slideshow.)

Entertaining stuff! Nothing in there about why Access uses Dec 30 1899. I've always had the impression that the Excel & the Access teams didn't get on but to disagree on that for no better reason would seem a bit extreme.

 

[The_Doc_Man]

Perhaps because Access and Excel weren't developed by Microsoft - they were purchased or absorbed or however you want to put it and then adapted to the office model. Bill Gates was very big about legally acquiring small companies to obtain rights to their products that he thought would be good. So they have different epoch dates due to having different origins.