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. 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 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 Opie (OP – Original Poster) 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.