Field to enter ONLY time. (3 Viewers)

[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'.

 

[gemma-the-husky]

I tend to use text for time only, with particular separators, and 24 hour clock. Check for minutes not exceeding 59

Eg, period or colon

So 2.30 or 2:30 would be allowed but 2.75 not allowed.

 

[Gasman]

Perhaps look at TimeValue?

? now()
26/07/2025 19:51:02 
? timevalue(now)
19:51:09

There is also a DateValue() function when you want only the Date.

 

[June7]

Interesting, when I type 14:00 into table field or bound textbox it is not showing date component. Shows 2:00:00 PM.
Format property is not even set to anything. When I click into field or textbox, date still does not show.

 

[Ken Sheridan]

Interesting, when I type 14:00 into table field or bound textbox it is not showing date component. Shows 2:00:00 PM.
Format property is not even set to anything. When I click into field or textbox, date still does not show.

In my case it shows as 14:00:00. I would normally set the Format property to whatever is the desired format, however, so the default format is immaterial. I'm guessing the default format set in Windows governs the behaviour in Access.

 

[Gasman]

If I type 14:00 into my date field I get
30/12/1899 14:00
As I want both, I will not change it.
However Medium Time will get 2:00 PM ?

 

[June7]

And where in Windows would be this setting to cause Access to show only time?

My System Tray is set to show date and time.

 

[Gasman]

And where in Windows would be this setting to cause Access to show only time?

My System Tray is set to show date and time.

If that is directed at me. it is a simple format option for the control.

 

[Ken Sheridan]

And where in Windows would be this setting to cause Access to show only time?

In Control panel. In Category view, select Clock and Region | Change date, time or number formats. The ensuing Region dialogue allows the various default date and time formats to be set. In my case the default time format used by Access corresponds to the Long time format set in the dialogue.

 

[Pat Hartman]

the datasheet is now showing correct but still the table in Access is still showing the whole 01/01/1900 14:00:00.

And that is as it should be. It is a mistake to use a format at the table level in an attempt to hide the true value of the table column. You could get a huge dent in your head and loose all your hair after days of debugging when you do this to yourself. NEVER format data in a table. PERIOD. The format does not change the stored value. It merely hides it from you. So you will drive yourself crazy trying to figure out why the two values that look like 2:30 are not identical.

The date/time data type is a "point in time" and time includes date. If the user must enter the time you probably won't run in to trouble with data values if you add some proper validation to the form's BeforeUpdate event. But if you use Now() to populate the field, you are in a world of hurt if you ever expect to compare two values because 2:30 PM may in fact not be exactly 2:30 since the date value is a double precision number and it is actually rounded to minutes and seconds for display but does not represent the actual stored value. Given that, I also store time as a string.

 

[June7]

Again, when I enter 14:00, field/textbox do not show date part. I do not have ANY formatting in table nor textbox. As far as I can tell, nothing in Windows settings would cause this.

I tested in 2010 and 2021, different computers.

I had expected date part to show, no idea why it is not.