Date and Time on a Date Field (1 Viewer)

Lilly420

Registered User.
Local time
Today, 01:32
Joined
Oct 4, 2013
Messages
126
Hello,


In Access, I have a table called tblMonarchPerformance that contains a Start Date and Finish Date…I have them set to the General Date Format which is supposed to include the time (or so I thought), yet the time does not display, all I see is the date.

I have a query based on this table where I need to show how much time was spent doing this task. Sometimes it can be in 20 minutes and other times, days but all I get is the date in the query and I would need to show the time as well…would anyone know how to format this so I get both? I did look online but could not find what I needed.

Thank you.

Lilly
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:32
Joined
Oct 29, 2018
Messages
21,358
Hi Lilly. How exactly do you populate these fields with date and time?
 

Lilly420

Registered User.
Local time
Today, 01:32
Joined
Oct 4, 2013
Messages
126
Hi there,

I have a form and the user puts in the start date and then the date when finished, puts in the date they finished...is that what you are asking? In the table, I have these two fields set to general which is supposed to include the time but I don't see it, just the date shows up.

Lilly
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:32
Joined
Oct 29, 2018
Messages
21,358
Hi Lilly. How exactly are the users entering the time part when they put the date in?
 

Lilly420

Registered User.
Local time
Today, 01:32
Joined
Oct 4, 2013
Messages
126
Hi,

They don't--they just put in the date, but I thought if I had the field formatted in the table as General Date (see below) it would include the time as that is what the format looks like...

General Date: 11/12/2015 5:34:23 PM

But it only shows the date, does not include the time...

Thank you and sorry if I am not clear.

Lilly
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:32
Joined
Oct 29, 2018
Messages
21,358
Hi,

They don't--they just put in the date, but I thought if I had the field formatted in the table as General Date (see below) it would include the time as that is what the format looks like...

General Date: 11/12/2015 5:34:23 PM

But it only shows the date, does not include the time...

Thank you and sorry if I am not clear.

Lilly
Hi Lilly. If the user does not put a time component, then the time will be 0:00 am or 12:00 am (midnight), and I believe Access, by default, will not display it. If you want to see all the dates with the midnight hour, then change the display format to a custom one like "mm/dd/yyyy hh:nn:ss ampm"


Hope it helps...
 

Lilly420

Registered User.
Local time
Today, 01:32
Joined
Oct 4, 2013
Messages
126
Hi, thank you and I did try to put this on the format of the table field and it does display time but not when I entered the date like 7/29/2019 12:13PM, it just puts in 7/29/2019 12:00:00AM. I need to track the time they spent working on a task...like if Started on 7/29/2019 12:13PM and then they would select the Finish Date: 7/29/2019 1:15PM (if that is when they entered the date)...does this make sense - so using the computer clock? Can it not be accomplished by just selecting a date field, do I need to add a time as well?

Sorry if I am confusing everyone...normally we just do dates and it hasn't been an issue but a lot of these tasks are done in that same day.

Thank you.

Lilly
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:32
Joined
Oct 29, 2018
Messages
21,358
Hi, thank you and I did try to put this on the format of the table field and it does display time but not when I entered the date like 7/29/2019 12:13PM, it just puts in 7/29/2019 12:00:00AM. I need to track the time they spent working on a task...like if Started on 7/29/2019 12:13PM and then they would select the Finish Date: 7/29/2019 1:15PM (if that is when they entered the date)...does this make sense - so using the computer clock? Can it not be accomplished by just selecting a date field, do I need to add a time as well?

Sorry if I am confusing everyone...normally we just do dates and it hasn't been an issue but a lot of these tasks are done in that same day.

Thank you.

Lilly
Hi Lilly. I just gave it a try and got the following result:
 

Attachments

  • start.PNG
    start.PNG
    5 KB · Views: 200

Lilly420

Registered User.
Local time
Today, 01:32
Joined
Oct 4, 2013
Messages
126
Hi again,

No idea what I am doing wrong. I opened the table, and on the Start Date, under Format I put in the format given above and did the same on Finish Date...and then I entered a few dates on the form and then looked at the table and they all say 7/29/2019 12:00AM...even though it should be 7/29/2019 12:36PM right?

thank you for the help.

Lilly

mm/dd/yyyy hh:nn:ss ampm
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:32
Joined
Oct 29, 2018
Messages
21,358
Hi again,

No idea what I am doing wrong. I opened the table, and on the Start Date, under Format I put in the format given above and did the same on Finish Date...and then I entered a few dates on the form and then looked at the table and they all say 7/29/2019 12:00AM...even though it should be 7/29/2019 12:36PM right?

thank you for the help.

Lilly

mm/dd/yyyy hh:nn:ss ampm
Hi Lilly. Just as a quick test, what happens if you blank out the Format property? Also, what is the BE table in this case?
 

Lilly420

Registered User.
Local time
Today, 01:32
Joined
Oct 4, 2013
Messages
126
Hi, when you say blank out the format property, do you mean not put anything in there correct? If so, I removed the formatting, and left it blank and it just gives me a date and nothing else. And you say what is the BE table...I am not sure what you mean here? Sorry...thank you for your patience.

Lilly
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:32
Joined
Oct 29, 2018
Messages
21,358
Hi, when you say blank out the format property, do you mean not put anything in there correct? If so, I removed the formatting, and left it blank and it just gives me a date and nothing else. And you say what is the BE table...I am not sure what you mean here? Sorry...thank you for your patience.

Lilly
Hi Lilly. Yes, I meant for you to leave the format property/box empty. Now, when you only put dates in the field, Access won't display the time. But if you add a time, it should show up. So, just for one record, probably the first one, try adding a non-midnight time just to see if the table will display it. For example, manually enter the following: 7/29/2019 10:35 am


As for my other question, I was wondering if the table is an Access or SQL Server table.
 

Gasman

Enthusiastic Amateur
Local time
Today, 05:32
Joined
Sep 21, 2011
Messages
14,048
Are you actually entering the time element as well as a date?
 

Lilly420

Registered User.
Local time
Today, 01:32
Joined
Oct 4, 2013
Messages
126
hi, OK, I entered the date 7/29/2019 1:40PM in the field on the form and it took it...and put this data into the table...so I guess my question was and maybe I did not phrase it correctly, I thought if you the field formatted as general when you entered a date it would put in the time of entry as well--BUT you are saying we need to enter the date and the time correct? This is not done automatically...that is what I was missing...so sorry.

This is a table in an Access Database that I built...does that answer your question?

Thank you for being so kind.

Lilly
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:32
Joined
Oct 29, 2018
Messages
21,358
hi, OK, I entered the date 7/29/2019 1:40PM in the field on the form and it took it...and put this data into the table...so I guess my question was and maybe I did not phrase it correctly, I thought if you the field formatted as general when you entered a date it would put in the time of entry as well--BUT you are saying we need to enter the date and the time correct? This is not done automatically...that is what I was missing...so sorry.

This is a table in an Access Database that I built...does that answer your question?

Thank you for being so kind.

Lilly
Hi Lilly. Okay, thanks for the clarification. Right, Access will not display the time component if it's midnight. So, if you want midnight to show up, you'll have to use a "custom" format (like the one I used in my sample). Here's what it looks like on my machine:
 

Attachments

  • dateformat.PNG
    dateformat.PNG
    10.6 KB · Views: 193

Dreamweaver

Well-known member
Local time
Today, 05:32
Joined
Nov 28, 2005
Messages
2,466
Hi from what I've read you are using this to track a users actvaty but trying to do it with one field and relying on access to add the correct time I would use 2 fields one where the user enters the date and another set for a default or after update of date entry field if not a new record of now() which will add both componts I.E. Date and Time.


hope it's of help


mick
 

Lilly420

Registered User.
Local time
Today, 01:32
Joined
Oct 4, 2013
Messages
126
Great, thank you for all the help, I appreciate it, really.
 

Users who are viewing this thread

Top Bottom