Converting date in a form

[Machi413]

I have one user who inputs her dates using a period (today would be 4.27) and Access is taking that as 4:27 AM. I don't want to change the input mask and mess up the other 8 people using the same input form. Suggestions are appreciated.

[isladogs]

Shouldn't your user be entering dates including the year e.g. 4/27/2022?
You could always use the Replace function to replace any periods (.) with /

Replace(Name of your date control here, ".","/")

What is the input mask you are using?

[Machi413]

I'm not currently applying an input mask on date fields in my forms since some use 4-27 and others 4/27, both of which are understood in Microsoft as 4/27 of the current year. Even if my [difficult] user put the year with her periods (4.27.22), Access sees that as 4:27:22 AM. I've tried directly replacing the dot with a dash or slash several ways using Replace but cannot get it to work. I'm fairly new to Access, and though I have created 50+ queries and forms, this issue has me stumped.

[isladogs]

Just tested this using a field with a date/time datatype.
I'm based in the UK where the date format is dd/mm/yyyy

Entering 27/4 or 27.4 or 27-4 are ALL correctly interpreted as 27/04/2022

[zmbd]

How about a nag box??
In the KeyPress event of the control

Private Sub Text0_KeyPress(KeyAscii As Integer)
  If KeyAscii = 46 Then MsgBox "Please do not use periods/dots in dates", vbCritical, "Invalid Key"
End Sub

I like this one as the feedback is immediate.
Or in the BeforeUpdate event you can use

Private Sub Text0_BeforeUpdate(Cancel As Integer)
  If InStr(Text0.Value, Chr(46)) Then
    Cancel = True
    Text0.Undo
    MsgBox "Invalid Date Format - Please do not use periods/dots in this field", vbCritical
  End If
End Sub

This will wait until the user presses enter/tab/clicks out of the date control - if there's a period then the nag, cancels the update and reselects the control.
Keep in mind that no matter where in the world you are, internally Access will ALWAYS handle dates in #MM/DD/YYYY# format - in tables, code, etc... it's been a source of errors in numerous posts from locations outside of the USA.

[NeoPa]

I believe this is a situation where the user should bend to the system rather than vice-versa. I see no rational reason why any system should cater for someone failing to use it properly. Unless you have a valid format where dates are entered that way of course, but I know of nowhere that would be the case.

zmbd:
Keep in mind that no matter where in the world you are, internally Access will ALWAYS handle dates in #MM/DD/YYYY# format - in tables, code, etc... it's been a source of errors in numerous posts from locations outside of the USA.

I'm not sure I follow you. Internally dates are stored as floating point numbers. Display formats are not in any way relevant to how data is stored.

Alternatively, #mm/dd/yyyy# formats are also not universally supported. An example is here in the UK. Date entry in a form will recognise dd/mm/yyyy where possible to interpret that way and only use mm/dd/yyyy when it cannot.

EG. 1/8/2022 will be treated here as 1st August 2022 rather than 8th January as it would be across the pond. Where you see the overlap is for something like 1/13/2022 which, even here, is recognised as impossible when interpreted as dd/mm/yyyy, so is automatically translated to mm/dd/yyyy.

More on dates etc can be found at Literal DateTimes and Their Delimiters (#).

[zmbd]

NeoPa: I'm not sure I follow you. Internally dates are stored as floating point numbers. Display formats are not in any way relevant to how data is stored.

Yes, I over simplified, internally the date is stored as a floating point number.