Date format tied to PC regional settings question.

[k3ll1n]

Evening all,

I've noticed an issue with my DB when I brought what I created at home, to the office. It seems that because my home PC and office PC had different regional short date format settings in the control panel, Access wasn't playing nicely. My office PC was formatted as 2016.06.08. Access would spit out an SQL error because if didn't like the periods in the dates. My home PC was fine with 2016-06-08.

My question is, if I create a ACCDE at home and put it on a shared drive for all the computers at work, when they enter the appointment date in the field, who's regional setting will it be using?

The creator of the ACCDE (at home)
The system of which the shared folder is on? (unknown office drive somewhere)
or the users system that is entering the data? (office)

What if one of those users had their date set to use periods as mine was for some reason. Is there something in Access that'll allow it to read periods also? Or actually use the format I set IN Access instead?

I've tried an input mask also but that still changes based on regional settings. (ie: type in "yyyy"/"mm"/"dd" and hit enter. Changed to "yyyy"-"mm"-"dd" as this is how my PC is set)

Any advice is appreciated.
I'm still very much new to Access.

Cheers,
k3ll1n

 

[Galaxiom]

It will use the settings of the PC where Access is running.

The forward slash is generally a placeholder for the regional date delimiter and Access will substitute the delimiter. (The placeholder happens to be the same as the delimiter in some regions.)

The Format() function can be forced to use the actual forward slash by using the literal escape, the backslash.
Format(mydate, "dd\/mm\/yyyy")

But remember the output of Format() is a string. If you use that string as a date it will be implicitly cast to date according to the regional settings. If it can't convert to a valid date in the regional settings it will try the US and ISO formats and quietly accept it if it is valid. For example 29/2/15 will come over as 15 Feb 1929. This is a Windows feature and is not limited to Access.