Display formats ignored/overridden

[jimatqsi]

I have observed a number of instances where it seems display format specifications I set in design mode are ignored at runtime. For instance, a form I'm currently working shows a number of dates in text boxes. I specify format of mm/dd/yy, but what I see at runtime is mm/dd/yyyy.

I've got a report that I specified a format of #,###;#,###;~ but what displays is a number with 2 decimal places.

Now I have many, many examples where I get what I expect to see. What am I missing here?

Jim

[zmbd]

Where are you setting the format?

[NeoPa]

4-digit date format in Options?

It can be set globally or by database.

[jimatqsi]

I'm setting the format in the properties of the text box in each case. If it was a global setting I would expect to see the problem in every instance, but that is not what is happening.

[zmbd]

Does this happen on multiple PC's?
The reason I ask is that regional settings in the OS can play with how things are displayed.

[jimatqsi]

I haven't checked it yet on any machine but the one I develop on. I'll take a look at some others today.

[NeoPa]

JimatQSI:
If it was a global setting I would expect to see the problem in every instance, but that is not what is happening.

It's not necessarily global. That's one of the possible options. Did you check the setting?

[jimatqsi]

I checked the 4-digit year date setting in Access options and it is not selected. Not for the particular database and not globally.

I was able to solve the problem with the decimal places showing in spite of my formatting in design mode. The reason for that problem was that the data was coming from a view in SQL and the data for those total columns (sums) was being return as strings. I'm not sure why that is, but changing the view to cast the data as integers solved the problem.

Jim

[zmbd]

coming from a view in SQL

That would have been good to know. I had assumed the data was in a Access database.

I have seen this before from other servers. We have a MySQL server that I pull data from into either Access or Excel and quite often data types will come thru as string-cast instead of Date or Numeric... To the point now that I've code that walks thru the records and explicitly sets the field/column to the correct typecast.

[NeoPa]

Thanks Jim. Answer very clear now.

As for stuff coming from SQL Server, that's a whole different ballgame. It will depend on the types of the fields included - whether from the tables or calculated - and also on the driver being used. If any data comes across via the driver as a type that Access can't recognise immediately then it's likely to be converted implicitly to some other type. Most frequently String.

As ZMBD says, this is really a detail that should have been included in the OP. There again, I'm guessing if that were as obvious to you before as it was to us, then you would have done so without further prompting. We all live and learn.