Format Condition for unbound Date controls uses US date format regardless of Regional Settings

[kentgorrell]

Only seems to be an issue comparing dates in unbound controls

Typically this is used for Report Criteria
Bound controls act as advertised
resolved by using
Expression Is: Format([txtStart_Date],"yyyymmdd")>Format([txtEnd_Date],"yyyymmdd")

Version 2311 Build 17029.20140
Region > Format: English (Australia)

 

[Pat Hartman]

Try setting the format of the unbound control to short date. That will tell Access that the control will hold a date data type field and should fix the problem. Otherwise the control's data type would be variant and lacking anything more specific, default to text.

 

[kentgorrell]

If only it were that simple. The control knows it is a date from its format property, be it short date or whatever. Type in "2/1" and you get 02/01/2024

The issue is that that the Format Condition does not recognise it as a date regardless of the control's format property or does not respect the system's regional setting and just interprets the value as being a US formatted date. But there seems to be more to it. Even a later month date in the previous year gets the wrong result.

Anyhow, the Format function does recognise the correct date, given the regional setting, and converts correctly. Then once converted to yyyy-mm-dd the condition evaluates correctly as either date or text.

 

[ebs17]

? Date * 1, #2/29/2024# * 1, Now * 1
 45352         45351         45352,3777893518

DateTime values are internally numbers, which can be shown with a simple multiplication.
So when formats get in the way, we think and calculate in numbers.
The inner value counts more than an external view.

the Format function does recognise the correct date

The function converts a DateTime value to a string and outputs this string as the return value.
So this is no longer a date, but a text that looks like a date. If you wanted to reckon with texts in the sequel, you have to be very careful.

 

[kentgorrell]

Yes most of us know that date time is a number, left of the decimal point is date, right is the time (at least in Access and VBA not so much in SQL Server which uses two integers). But here we are only concerned with the integer Date Value and how the UI deals with it and converts it to that integer value. This the issue. How Access converts the value, that we see, to the integer/date value based on the regional settings and format property is the issue. Specifically, in this case, how the Format Condition evaluates the date.

re:"If you wanted to reckon with texts in the sequel, you have to be very careful."
what does that mean?

The whole point of converting to yyyy-mm-dd is that it is safe and makes no difference whether Access (or SQL Server for that matter) treats it as a date or a string. So NO you do not need to be careful for either sorting or comparison (eg greater than or less than). Once it is in the international format it is safe and reliable for both of these purposes as either a date or a string.

your statement "The function converts a DateTime value to a string and outputs this string as the return value. So this is no longer a date, but a text that looks like a date"

As you pointed out the date is not a date it is a number that looks like a date. And the way it 'looks' can vary with settings and context.

Once converted to yyyy-mm-dd then 'what you see is what you get', unlike short date formats.
TSQL will recognise this string as a date (you just need it encompassed by single quotes). Same goes for Access SQL if you encompass the result with hashes eg #2024-02-01# and even VBA where you could also convert it to date data type using cDate(). So even if you want to calculate a datediff (in VBA Access SQL or TSQL) which relies on the integer/date value then yyyy-mm-dd will give you a reliable result.

Access SQL expects mm/dd/yyyy as opposed to dd/mm/yyyy (regardless of your regional settings) and may trip you up if you don't live the the USA but yyyy-mm-dd will be reliably interpreted as intended regardless of your regional settings.

 

[Pat Hartman]

I've seen two strange results from the Conditional Formatting this week. Looks like you need to format the control in the Conditional Formatting dialog and the yyyy/mm/dd format is unambiguous so it is more widely used.

 

[DickyP]

Has always been a problem with Microsoft's usage of the minority use US date format, which is only used by 3% of world nations according to the UN*. Being a Brit I always use dates formatted as dd/MMM/yyyy in Access and other Office apps, having had exactly the same problem many times over the years.

I'd have the condition above as [Value] = CDate(Format([txtStartDate],"d/MMM/yyyy")) which I know works. The best bit is that having the month in letters leaves any underlying interpretation as unambiguous. I'd aslo have the unbound control formatted as dd/MMM/yyyy.

The ISO_8601 format YYYY-MM-DD is now supposed to be an international standard. https://en.wikipedia.org/wiki/ISO_8601

* - the UN figure are: DMY 63%, YMD 31% , MDY 3%, undecided 3%.