Using military date/time format

[WCStarks]

I really do not like having to deal with entering and displaying times the way Access wants it done. I would really prefer to enter and display times as 4/14/2020 0715, or 4/14/2020 2140, without the colons. However, I have never been able to figure out how to do it. Is there really no practical way to make Access work with times in military format?

[Micron]

Not sure if this will help without knowing more about what you're doing, but storing the date and time in one field is easy enough by using Now function. If you want to show date and time separately on a form, format the bound date field as a date format that you like, and an unbound textbox formatted to show Time as military time. Could be as simple as
=Format(yourDateField,"hh:mm")

[CJ_London]

use wither the format property or the format function. This is using the function

?format(now(),"m/d/yyyy hhmm")
4/14/2020 2331

[WCStarks]

The main thing is to be able to enter the date and time without using the colon and have Access accept it.

[Cottonshirt]

I really do not like having to deal with entering and displaying times the way Access wants it done.

what you have here are two different issues, and whichever solution you adopt will end up being a compromise on at least one of those issues. however, ACCESS is a computer program and is inherently unable to compromise, and you don't sound as though you are prepared to compromise, so I predict this is not going to end well. but, I can provide you with a road map that might help you find a solution that works.

time is stored in ACCESS as the decimal portion of a number less than 1. the number represents the proportion of one day taken up by the time indicated. the number 0.5 represents half a day and is therefore the time for mid day, and the number 0.041666666... = 1/24 and is therefore one o'clock in the morning. when ACCESS displays a time as 07:15 it is simply formatting the underlying number in a way that the majority of the ACCESS user population find readable, usable, and convenient.

what you want to do is input what most folk would consider to be a 4-digit number in the format hhmm, have ACCESS interpret that as hh:mm, which it will then convert into a decimal number.

let's first consider the mathematics of getting that done. suppose you want to enter the military time 0915.

to convert that to a decimal number you take 09 hours and multiply by 60 to get 540 minutes

add the 15 minutes to get 555 minutes

multiply by 60 to get 33300 seconds

then your time is calculated as 33300/86400 = 0.385416666 days.

so one approach would be to write a procedure, which means designing and coding your own ACCESS function, to take as input your 4-digit number and convert it into a decimal number that ACCESS stores as a time.

this is eminently do-able, but I have no intention of doing it for you. if you want to do this then you are going to have to do some work yourself. and it is worth considering that this only does the first half of what you want. it takes an input from you and stores it as a time. time, in ACCESS, will still be displayed in the old-fashioned colon format as 09:15, and to get that displayed in the military format you would have to write a second function to, essentially, undo what the first function did and display 0.385416666 as 0915.

to some extent I sympathise with you because ACCESS doesn't display time in the way that I want, so I wrote a function that displays time exactly how I want it, but I compromised on the data entry part to get the output I prefer. and I suspect that you may, ultimately, have to do the same.


many thanks,


Cottonshirt

[WCStarks]

Thanks for your response. My biggest issue is having to deal with the colon on data entry. Another compromise would be to do what Access likes, by continuing to use the colon, but have Access enter it for me, so I can avoid the awkwardness of entering it myself. After my last post, I found that the Input Mask does just that. I played around with the Input Mask and got it working as 00/00/0000 00:00;0. I don't need seconds. The data-entered OK, but for some reason, the date now became too long for the display and would only display a long string of x's. So, I scrapped that for the time being. I will try again today. I see that the second parameter should really be 1, to avoid storing the the formatting characters in the record.

I understand how the Julian Day Number works. In fact, some 25 years, I wrote a perpetual calendar/world clock program using dBase/Clipper. It stored the dates and times as Julian Day Numbers. I also allowed it to store partial dates, which Access will not allow. It could accept a dd mmm yyyy, mmm yyyy, or just yyyy. mmm yyyy was stored as the 0th day of the month, and yyyy was stored as the 0th day of the year. This also allowed partial dates to sort before full dates, which was convenient. As I recall, the JDN begins at noon in 4,713 BC. I suspect the Access date/time number begins in 1900. It was interesting, when displaying the JDN, to watch the seconds, even fractions of a second tick off. The program was designed to help with genealogy research. It converted between the Gregorian and Julian calendars, and did conversions for the French Republican Calendar, English Regnal Calendar, and Ecclesiastical Feast Dates, among others. It also had a range of 1 AD to 4K AD, accounting for Easter and leap years. It could have gone BC, but I never got around to working that out.

I'll play around with the input mask some more today and see what happens. I don't understand why the dates/times entered using the mask would take more room to display than entering without the mask. Any ideas? May it have something to do storing the format in the record, or perhaps with not including seconds in the mask? The interface uses 24 Hour time and allows only enough space to include down to minutes.

[WCStarks]

Actually, when I use the mask 00/00/0000 00:00;1 created in the input wizard, it shows up in the field properties as 00/00/0000\ 00:00;1;0;. While the mask seems to work in the wizard, it does not work in an actual form field. The mask delimiters are not shown in the field. It produces 3/14/2020 08:40:00 AM, which is too big for the display. When I delete the seconds and the AM, the field errors out, saying it is not input according to the mask. So, I don't know what is going on.

Even after removing the mask, the field remained messed up. Since I hadn't saved the changes, I canceled the edits, to get back to the original form before the edits. I really can't figure out how to make the mask work.

[WCStarks]

_____________________

[CJ_London]

try this

00/00/0000\ 00:00;0

[pbaldy]

And you can format the textbox with

mm/dd/yy hh:nn

[WCStarks]

The last zero just tell it to store the formatting characters in the record which uses up extra space. I had tried that earlier and changed it to 1 to save the storage space.

[WCStarks]

And you can format the textbox with

mm/dd/yy hh:nn

I already have that in text box format property. I can enter the dates/times ok. I was just trying to use the mask to avoid having to enter the colon manually.

[pbaldy]

Sorry, I thought you said it was displaying with AM/PM. Did the mask work? I tested and only had to enter

0415201013

to get

4/15/20 10:13

[WCStarks]

I can enter that way in the mask wizard, but it doesn't enter that way in the actual field. With the mask set, the date/time is messed up for the existing date/time. It messes up the field, showing 04/14/2020 08:40:00 AM, which is too big for the display size of the field. If I delete it and try reentering the date, the entry follows the format property, not the mask property. I had the text box format set to mm/dd/yyyy hh:mm at the same time I tried to use the mask. I wonder if that is causing a conflict?

I just tried using the mask without the textbox format property set, but it still does the same thing -- 04/14/2020 08:40:00 AM.

[pbaldy]

Can you attach the db here? It is working in my test db.