Date entry format : 08/11/2018 1350

[WCStarks]

I am currently entering time in the date field as 13:50, but I would like to be able to enter 24 hr time in a date/time field as 1350, as is done in the military and other entities, with no ":". I would like it for both data entry and display. I can get 08/11/2018 13:50, but not without the ":". I have searched the Access date/time formatting helps, but I apparently do not understand them well enough and cannot figure out how to do it so it works. I have attempted to use an input mask, but I can't get it to work for me. Any suggestions?

Also, I cannot get the hours to display with a leading 0, when needed.

In the control's Format property I have: mm/dd/yyyy hh:nn, but if I try to leave out the ":" in the format, the times don't work right.

[ranman256]

Because time requires ':'. Otherwise it's not time.

[isladogs]

I've just tested this in the Immediate window and it worked fine for me.

?format(Now(),"mm/dd/yyyy hhnn") gave 08/22/2018 2006

Repeating a few minutes later, I did this to test the 0 in the hours

?format(Now(),"mm/dd/yyyy hhnn ampm") => 08/22/2018 0810 P

I didn't try it in a form control
Also as Ranman indicated, I very much doubt you can use these in any time calculations

[WCStarks]

Yes. I was referring to the form's Format property.

I am not sure how I would use the format() function in place of the control property, for data entry.

I have used the Format() in reports at times, to fix the missing leading zero, but it is still awkward in forms.

It seems one should be able to have more flexibility using the properties' format feature, as it involves less busy work.

[isladogs]

Here are 3 unbound form controls formatted in slightly different ways.
The control source = Now() in each case



What you MAY find confusing is that whilst a control has the focus it will show the default format including seconds
e.g. 08/22/2018 21:17:45
It will revert to the set format when the focus is elsewhere

You don't see that effect with reports as in normal use, these aren't interactive i.e. not editable

Otherwise, I don't see why form control formatting is any more awkward than report control formatting
Choose from a list of common formats or create your own as appropriate

[WCStarks]

Are you using the form Control Format property?

[WCStarks]

When I specify mm/dd/yyyy hhnn in the control format property (no quotes), it will convert an existing time with no error, but when I enter a time this way, it displays it as such, but also gives me an error message that the value entered is not valid for this type. And when I go back to it, it will report the error again each time. However, If I enter the time as 22:00, it will display it as 2200, and not report an error on entry or on refocusing the field. It doesn't really accept 2200 as a time on data entry. So, I am still forced to use a colon on data entry, which I am trying to avoid.

[pbaldy]

You're entering date and time together? I typically have them entered separately so I have input masks for each, but try this which seems to work to let me enter "0822181438" and get "08/22/18 14:38"

##/##/## 00:00;0;_

[WCStarks]

That does work, but it displays the mask in the input field. I would prefer not to see it, but if that is how it works then that is how it is. What does that last 0 mean?

[pbaldy]

What does that last 0 mean?

https://support.office.com/en-us/art...2-4a47832de8da

[WCStarks]

I tried to make the time optional, as there are times when I don't need to enter a time, but putting 99:99 in the template doesn't do it, as the MS help for Input masks suggests. I guess I'll have to enter 0000 in those cases.

[isladogs]

When I specify mm/dd/yyyy hhnn in the control format property (no quotes), it will convert an existing time with no error, but when I enter a time this way, it displays it as such, but also gives me an error message that the value entered is not valid for this type. And when I go back to it, it will report the error again each time. However, If I enter the time as 22:00, it will display it as 2200, and not report an error on entry or on refocusing the field. It doesn't really accept 2200 as a time on data entry. So, I am still forced to use a colon on data entry, which I am trying to avoid.

If you are using a datetme field, the data has to be stored as a standard date/time in Access (which is actually a double number datatype).
This means it must be entered using the colon though using a custom input mask will help.
What the formatting does is display the data however you want.

If you don't want to include the colon for data entry, use a text datatype instead.
But then it won't behave like a date. Up to you

[WCStarks]

I notice an odd side effect. I cannot correct a portion of the date/time without causing an error. I must re-enter the whole date/time. If I delete the whole field, the input mask is not visible. If try to re-enter the date/time in this state, it errors out. I must delete, leave the field and return to the field, to get the input mask to display, before I can re-enter the date/time.

[WCStarks]

Well, I need the records to sort like a date.

[isladogs]

Well if it walks like a date, talks like a date and sorts like a date then it must be a date

So you need to use a date field and put up when the colon in data entry
TINA (there is no alternative!) AFAIK ....

Input masks can cause irritating behaviour as described in post #13
That's why I very rarely use them