Field to enter ONLY time. (1 Viewer)

[Jen-Jen]

Hi.
In a table I've put a field for date as I wanted the drop down calendar option in the form for the users and there's another one separate field for the time.
All works well in the form when entering and viewing the data in the form, however when you select the field to change the time, it shows "01/01/1900 14:00:00"
(14:00 is what was entered.)
And when you go into the table or another datasheet form with those fields, they are showing 01/01/1900 14:00:00.
We don't want the date in the 'Time' field. And why 1900? and not the year or date we're in??
For date in the table you can choose 'Date Only'. How do you do that for Time?? or what do you have to do??

Thank you

 

[Trevor G]

Have you set the format in the table for the time field? You would use something like hh:mm which you would use and not the date in the format in the time field properties.

 

[Gasman]

Hi.
In a table I've put a field for date as I wanted the drop down calendar option in the form for the users and there's another one separate field for the time.
All works well in the form when entering and viewing the data in the form, however when you select the field to change the time, it shows "01/01/1900 14:00:00"
(14:00 is what was entered.)
And when you go into the table or another datasheet form with those fields, they are showing 01/01/1900 14:00:00.
We don't want the date in the 'Time' field. And why 1900? and not the year or date we're in??
For date in the table you can choose 'Date Only'. How do you do that for Time?? or what do you have to do??

Thank you

The reason for that date is because a date in Access is actually a number.
Before the decimal point is the date, and after it is the time.

? cdec(now())
 45863.4606828704 
? now()
25/07/2025 11:03:40

As your date is 0, that is 01/01/1900.

Simples, yes?

 

[CJ_London]

Just to clarify further the date number is the number of days since 1900/01/01

 

[arnelgp]

Just to clarify further the date number is the number of days since 1900/01/01

isn't it Dec-30-1899?
since this CDate(0).
and Format(CDate(0),"mm/dd/yyyy") = 12/30/1899

this is what CoPilot explains:

In Microsoft Access, 01/01/1900 is often seen as a default or placeholder date—but it’s not the true "zero date" in Access. That honor actually goes to December 30, 1899, which represents day zero in Access's internal date-time system.

Here’s how it works:

• Access stores dates as floating-point numbers, where the integer part counts days since 12/30/1899.
• So, 01/01/1900 is day 2.
• The decimal part represents the time of day (e.g., 0.5 = 12:00 PM).

Why does this matter?

• If you store only a time (like 6:00 AM), Access still needs a date, so it defaults to something like 12/30/1899 or 01/01/1900 depending on how the value was created.
• This can cause confusion when importing data from Excel, which uses 01/01/1900 as day 1, leading to a one-day offset between the two systems3.

If you're seeing 01/01/1900 in your tables or queries, it might be:

• A default value for uninitialized date fields.
• A result of importing from Excel or SQL Server.
• A time-only value where the date portion defaults to 01/01/1900.

 

[arnelgp]

And when you go into the table or another datasheet form with those fields, they are showing 01/01/1900 14:00:00.

the field is Date/Time (so it is Date + Time).
you can suppress the Date portion by Editing the table structure and adding hh:nn in the Format Property
of the Date/Time field. but this is Only for display, the Date portion is still there.

 

[Jen-Jen]

The reason for that date is because a date in Access is actually a number.
Before the decimal point is the date, and after it is the time.

? cdec(now())
 45863.4606828704
? now()
25/07/2025 11:03:40

As your date is 0, that is 01/01/1900.

Simples, yes?

Oh ok. that explains the date but what about the time? We have hours and seconds world wide, what are the 3rd 2 digits for? 14:00:00 ?

Have you set the format in the table for the time field? You would use something like hh:mm which you would use and not the date in the format in the time field properties.

These tables are in back-end in Sharepoint so won't let me change locally. I went to Sharepoint and put hh:mm in the list setting and the datasheet is now showing correct but still the table in Access is still showing the whole 01/01/1900 14:00:00.
Love Access but sometimes you've got to wonder.

 

[arnelgp]

what are the 3rd 2 digits for? 14:00:00 ?

the format is:

hh:nn:ss

you can create a form (datasheet or anything) against that table and set the format of the Textbox on design view of your form.

 

[Jen-Jen]

the format is:

hh:nn:ss

you can create a form (datasheet or anything) against that table and set the format of the Textbox on design view of your form.

oh yea! of course! I got confused!

 

[Ken Sheridan]

I would suggest that you also apply a ValidationRule property of Is Null Or (>-1 And <1). That will prevent a value other than a time only being inadvertently entered. I don't know whether a Sharepoint list allows this, but. if not, you should at least do it for the bound control in your data input form.

You can omit the Is Null if the column disallows Nulls, i.e its Required property is True.

The reason for including >-1 in the expression BTW is that DateTime values before 1899-12-30 00:00:00 are implemented as negative numbers in Access. It's unlikely such a value would be entered, but as the great Capt Murphy informs us, 'If something can go wrong, it sooner or later will go wrong'.