470,632 Members | 1,317 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,632 developers. It's quick & easy.

Converting date in a form

2 2Bits
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.
4 Weeks Ago #1
6 11540
isladogs
354 Expert Mod 256MB
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?
4 Weeks Ago #2
Machi413
2 2Bits
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.
4 Weeks Ago #3
isladogs
354 Expert Mod 256MB
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
4 Weeks Ago #4
zmbd
5,445 Expert Mod 4TB
How about a nag box??
In the KeyPress event of the control
Expand|Select|Wrap|Line Numbers
  1. Private Sub Text0_KeyPress(KeyAscii As Integer)
  2.   If KeyAscii = 46 Then MsgBox "Please do not use periods/dots in dates", vbCritical, "Invalid Key"
  3. End Sub
I like this one as the feedback is immediate.
Or in the BeforeUpdate event you can use
Expand|Select|Wrap|Line Numbers
  1. Private Sub Text0_BeforeUpdate(Cancel As Integer)
  2.   If InStr(Text0.Value, Chr(46)) Then
  3.     Cancel = True
  4.     Text0.Undo
  5.     MsgBox "Invalid Date Format - Please do not use periods/dots in this field", vbCritical
  6.   End If
  7. 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.
4 Weeks Ago #5
NeoPa
32,301 Expert Mod 16PB
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 (#).
3 Weeks Ago #6
zmbd
5,445 Expert Mod 4TB
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.
3 Weeks Ago #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by wireless | last post: by
3 posts views Thread by Gary Smith | last post: by
3 posts views Thread by NateM | last post: by
9 posts views Thread by Alok yadav | last post: by
3 posts views Thread by Jef Driesen | last post: by
4 posts views Thread by gubbachchi | last post: by
1 post views Thread by =?Utf-8?B?bGF3ODc4Nw==?= | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.