Regional settings cause report to fail in Access

Here’s a weird problem that I encountered recently. In a database that I had developed for a client, one of the reports refused to run when they switched their regional settings to another language — a problem, since some of the users were German and hence would be unable to use the report without changing back to English settings first.

The problem came down to the headers that I had used in the crosstab query that fed the report. It had text strings like “01_Mon”, “02_Tue”, etc for a total of 10 headers. And of course, these became fields for the report.

What I hadn’t realised what that Access interpreted these as dates, so when the regional settings changed, they became “01_Mo”, 02_Di”, etc … and the report broke. The fix was simple: use generic “Day_01”, “Day_02”, etc as the day headers, and update the report controls to read from these generic fields.

I guess it’s another case of crosstab queries and reports having unintended consequences. They are powerful, but they can bite. Here’s hoping they don’t bite you the same way.

About sydneygeek

I was a consultant in Excel and Access for about 14 years before taking a full time role as an analyst with an energy company. Tech interests include Excel, Access, VBA, HTML / CSS and Oracle. I am based in Sydney, Australia and am a moderator on MrExcel, where I go by the name SydneyGeek.
Bookmark the permalink.