Time Gentlemen Please

Time Gentlemen Please

Using Date and Time in MS Access can be a bit of a minefield, so I suggest you do plenty of research on it. I’ve done a few video’s on Date/Time over the last few years and I’ve brought them together in this post

Add Time to a Date

Add Time to a Date

Video 1 (7:20)

In this video I demonstrate how to do date/time calculations in Microsoft Access. However, as I say in the video, you are much better off using the built-in date/time functions provided. This video is designed to give you some idea of the underlying construction of the date and time used in MS Access.

Video 1 (7:20)

Nifty Access YouTube Thumb Nail
Play Video

… …

Storing Date & Time

Storing Date & Time - Nifty Access

Video 2 (5:18)

Querying Dates can be quite a challenge in MS Access until you discover the little secrets. The access “Date Field” actually can (not always) “Show” the time value and this can be confusing. You can avoid problems with dates by using the date value function to extract only the date portion.

Video 2 (5:18)

Nifty Access YouTube Thumb Nail
Play Video

Add Time to Date VBA - Video Transcript

2016/01/19

Hello folks another MS Access YouTube Video, this time about “Time”!

Becky asked the question how to add 10 a.m. To a date in an MS Access date/time field This is the question in my MS Access Hints and Tips forum on Facebook This is the forum, here is the link. I will post the link separately in case you wish to join.

Although I’m British, I am showing this example with the American date format Month/Day/Year I’ve created a form which is bound to a table that contains a date field called fDate I also have a command button to run some VBA code I can enter a date into this textbox with the “date picker” button The text box is formatted for “General Date” you would expect it to display both the date and the time But it’s only showing the date portion.

Now I’ll enter the date directly See… it only shows the date portion In MS Access help, this is expected behaviour It might be expected but I reckon it’s confusing for beginners Now I’ll enter the Time directly (00:00:00) Look at the short date, it’s showing a date? But no dates have been entered. Again more confusing behaviour! Why that date? Why 12/30/1899?

Basically it’s the default date although it’s not quite as simple as that. It’s worth looking up on the Internet it’s quite an amusing story These are the built-in date formats. You also have the option of adding your own date format as shown in these textboxes. I hope you can see that it’s important to know about how the date is displayed.

The question was about a calculation so let’s move on. My interpretation of the question is that Becky wants to add 10 hours on to a date So let’s do some Addition Open the form in design view Note the name of this textbox:- “txtDate1” Open the code behind the form And write code to add one (1) to the textbox value Save, Close, close the form reopen the form Press the button and note that the date increases by one day So what happened? Going back to the code window All I’ve done is add 1 onto the value in the txtDate1 textbox So we can infer that 1 = 1 day. So what happens if we add half a day? Open the form again – and press the button Note that the date increases by 12 hours instead of 1 day (24 hours) Let’s go back to the code window. A day is 24 hours. What if we only add on 1/24th of a day in other words one hour? Open the form again and press the button Note that the hour increases by 1 on each press.

So to answer the question we need to add 1 hour 10 times Let’s go back to the code window If we multiply the one hour by 10 the button should now add on 10 hours on each press Open the form again,and let’s set the date to the date in the question which was 26th of January 2016 Now press the button and we have the correct date and time as requested by the OP.

Going back into the code window we could make some small changes For instance instead of having 1 ÷ 24 which represents one hour, we could create our own variable which represented one hour:- dHour = 1/24 Running the form again and pressing the button – nothing happens?

Returning to the VBA window Note that dHour has been dimensioned as an integer, this means it only Returns whole numbers Change it to a double and try again The reason you can add date and time like this is because the date is stored as a number:- Example >>> 36544.625 This method of adding date and time together is fine for small alterations.

However you could get unexpected results in some calculations. you should also look at date add the VBA functions DateAdd and DateDiff. Links mentioned in the Video Format Property –

Date/Time Data Type 

DateDiff Function 

DateAdd Function 

Dates And Times In …… 

… …

Links - Related to this Blog:-

The link(s) below relate to various discussion(s) related to this Blog. Visit these and you may find someone else has already solved the problem you are having. You could also pose a new question.

I also provide links to to any useful information I happen upon. I’m always updating these link collections and I keep a master collection here:- Useful Access URL’s

… …

More Useful Stuff HERE:-

This website uses third-party software - WordPress Add-Ins to be exact. I don't know what any individual add-in does, but I'm sure that many of them collect information about you. So be aware, if you continue using this site, then you are likely to be sharing your information. I don't know how to disable this sharing for any, or all of the plugins for individual users. So I can't stop the sharing of information. If this worries you then please do not use this site... If you continue to use this site I will assume that you are happy with it.

Do you need a hand in the right direction?

You are in the right place.