Forcing date format with session.LCID


I am trying to use VB to force my date format to be DD/MM/YYYY in Access. Some computers we use for data entry will have the session.LCID set to US time (and thus MM/DD/YYYY) and some set to UK time (DD/MM/YYYY). For the purposes of this question, let's just say that we can't change the setting manually in the Control Panel.

I want my field to stay in date/time format unless you have a really easy work-around for using a text field. The main reason is that I need to be able to make date calculations in this database.

I tried the following code into the VB code for my form, but it didn't work:

Sub Setlocale()
Session.lcid = 2057
End Sub

Call Setlocale

Any better ideas of how to achieve this?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Does Format(DateVariable,"General Date") do what you want?
ghaphisAuthor Commented:
>Does Format(DateVariable,"General Date") do what you want?

My concern is that when data is entered one some computers, it will be entered as D/M/Y but if the session.lcid is set to US English settings, it will switch it to M/D/Y. At other times, the data will not  be switched. This makes it difficult to sort out which dates are in the wrong format. I would rather just set the whole data entry system to UK English settings to ensure that it is all D/M/Y.

>See here

I tried to put the session.LCID = 2057 line in the Form_Load() private sub and in the a sub that runs after clicking a button. In both situations I got a "object required" error message. What am I missing?
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

ghaphisAuthor Commented:
I also see that I could possibly also use:

Thread.CurrentThread.CurrentCulture = new CultureInfo("en-gb")


SetLocale "en-gb"

I'm a beginner coder, so I'm not clear where I'm supposed to put these lines (if they are even correct). Modules vs. class module vs. within the code for a specific form. Ideally I would like these rules to apply to the whole database.

If you convert the date into a Date type variable and then use that variable to genrate a date string, it will not matter what format it was originally in. The Date data type is basically a number corresponding to a date. IF you say S$ = Format$(DateVariable, "dd/mm/yyyy") it will always display in that format.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ghaphisAuthor Commented:
But I would not be able to make date calculations with a string in date format, correct? I need to be able to make calculations.
What kind of calculations?

You can do the calculations within the program with the date held in the Date type variable, then use the Format$ function at the end when you want to actually display the date.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.