Access automatically converting regular numbers into Date/Time?

[TrulyVisceral]

Ok, I just don't really know what to do for the title, but the situation goes like this.

I'm working on a database for a small company for internship. They gave me a bunch of excel spreadsheets and told me to make a database that automatizes everything in it. Disregarding the fact I'm not THAT adept at Access (though I'm learning by the second) something odd was in these sheets.

The Help Desk app saves Date/Time as a string of numbers (eg. 43097.5061805556)
In Access, when I change the column type from number to Date/Time it automatically translates it into a date and hour (The previous number is 28/12/2017 12:08:54 PM).
Same thing in forms and reports when I change the imput mask to Date or Time.



1) Why is this? I was showed this by someone like some minutes ago, yet they didn't explain how it worked. And I don't really know HOW to search for it. Number strings into dates?

2) Is there an input mask for Date/Time? Not individually (One mask for short/medium date, and one for short/medium time) but one that displays it like the above example.

[orange]

Did you try google to see if there was an answer?
https://www.pcreview.co.uk/threads/h...-date.1103553/

To quote the legendary MVP John Vinson:

"Access stores Date/Time fields as floating point numbers, counts of
days and fractions of a day (times) since midnight, December 30, 1899.
The format merely controls how the value is displayed, not what's stored."

and

"Because in Access a Date/Time value is NOT A STRING. It's stored
internally as a double floating point count of days from a start
point; it can be formatted in many ways but underneath it's still just
a number."                     

 
It's a running count of days since midnight,
December 30, 1899 in days and fractional days. #06/01/2004 00:02:03#
is, for example:

?cdbl(#06/01/2004 00:02:03#)
38139.0014236111

Or going the other way,

?cdate(38250.6666666666)
9/20/2004 4:00:00 PM

Good luck with your internship.

[isladogs]

Dates are stored as double number types such as 43121.25
The integer part is the number of days after a start date of 30/12/1899 which was day zero
The decimal part is the time as a fraction of a day so 0.25 is 06:00 or 6am.

Access automatically handles date calculations using these number values
To convert the current date/time into the number value, use CDbl(Now) or today would be CLng(Date) where cDbl gives a double number and CLng gives a long integer

To convert back use e.g. CDate(43121.25)

EDIT Orange got there faster

[TrulyVisceral]

Thanks for the explanation there. Didn't really get it.

As for the rest...
Wait, hold up. Where do I put the CDate? The VB? In a Macro?

I mean, the values are gonna be saved as regular digits in the table, but I just want them displayed in the forms and reports as the date. So where does that CDate go then?

Or perhaps the other thing I asked for the input mask?

Also:

Did you try google to see if there was an answer?
https://www.pcreview.co.uk/threads/h...-date.1103553/

To quote the legendary MVP John Vinson:

"Access stores Date/Time fields as floating point numbers, counts of
days and fractions of a day (times) since midnight, December 30, 1899.
The format merely controls how the value is displayed, not what's stored."

and

"Because in Access a Date/Time value is NOT A STRING. It's stored
internally as a double floating point count of days from a start
point; it can be formatted in many ways but underneath it's still just
a number."                     

 
It's a running count of days since midnight,
December 30, 1899 in days and fractional days. #06/01/2004 00:02:03#
is, for example:

?cdbl(#06/01/2004 00:02:03#)
38139.0014236111

Or going the other way,

?cdate(38250.6666666666)
9/20/2004 4:00:00 PM

Good luck with your internship.

I said I searched for it, but didn't really get results because i just didn't know HOW to search it.
Also thanks. Much Appreciated.

[isladogs]

The date is stored as a number but displayed as a date in tables, forms etc
You shouldn't normally need to convert it at all unless you are importing as a number from an external source and saving as a date
I would recommend you do that and after that you need to do nothing else.

Both orange and I were explaining how dates are stored in a date/time field as that was what you asked about.

I suggest you don't use input masks as you'll probably get into a tangle doing so.

[TrulyVisceral]

The date is stored as a number but displayed as a date in tables, forms etc
You shouldn't normally need to convert it at all unless you are importing as a number from an external source and saving as a date
I would recommend you do that and after that you need to do nothing else.

Both orange and I were explaining how dates are stored in a date/time field as that was what you asked about.

I suggest you don't use input masks as you'll probably get into a tangle doing so.

Cool, but if I'm making a report, I would like it saved as a full date with hour. Not JUST date or JUST hour.

That might have come off as dickish, sorry.

[isladogs]

Input masks are used to guide users inputting data which seems to be irrelevant here
You use formatting to display however you want it to look
E.g Format([mydatefield], "dd/mm/yyyy hh:nn") where nn are minutes gives results like 27/03/2018 16:18
Many other formats available to choose from

[TrulyVisceral]

But where, VB? The property sheet?

[isladogs]

Property sheet for that report control​

[TrulyVisceral]

If only I could change my Access's language. It's hardwired into spanish and it won't change. Maybe it was a windows update.
I can't find the option. Explain like I'm 5 please.
I mean, I do know spanish, but the translation isn't literal.

[orange]

@TV,

You really should become more familiar with Google.

https://www.techonthenet.com/access/...0/basics03.php

https://support.office.com/en-us/art...5-ba24d881b698

You are working with VBA.

Good luck

[TrulyVisceral]

@TV,

You really should become more familiar with Google.

https://www.techonthenet.com/access/...0/basics03.php

https://support.office.com/en-us/art...5-ba24d881b698

You are working with VBA.

Good luck

Coolcool, I made this harder than I needed to.
In the format field of the property sheet for the date I was trying to format, it just gave me choices for numbers (Double, whole, etc... since that's how it's saved in the table). I ASSUMED that I needed to write some code or smth for it to work properly, but I failed to realize I can write things in that field that aren't the choices given. General Date, although not a choice in the dropdown, was all I needed to write. There, fixed.

That's it, that's all.