Custom Date Input Mask or User Entry Options

[MSommer]

I am replacing an Excel spreadsheet with an access database for better data structure, better user experience and reporting. For dates in the Excel file the users are use to being able to enter 8/2 or 8/2/23 and Excel formats it as 8/2/2023 (puts the year in for them and ignores leading zeros in the month and day). Those Excel columns are simply short date format, no code.

In my database the date fields in my forms are formatted as mm/dd/yyyy and I have a short date input mask on them. This means the users must enter the dates as 08252023 then it will auto insert the /. But if they try to enter 8/2 or 8/2/23 like they do in Excel, Access doesn't put the numbers in the correct place and throws an error.



I researched the input mask values to create a custom input mask and tried 90/90/9900 but that didn't work either. I insured the format was set as well as the input mask. Date picker is turned off. Is it possible to achieve the same functionality in Access for date input as Excel? Maybe even through VBA? I've been researching and cannot find much on the subject. It would be great if they could enter 8/2 and it result in 8/2/2023 or if the date was a different year than current year they could override and type 8/2/22 and it would store as 8/2/2022. Thanks for any ideas you may have.

[CJ_London]

suggest remove the input mask - often more trouble than they are worth.

As with excel, user would still need to type the /