Field Formatting

[access2010]

We are getting ready to upgrade our MsAccess databases and would like to have advice on the formatting of the fields that we have been using for years.

Attached is the different formats in our MsAccess 2003 database(s) and I would appreciate suggestions on if and how I should change the field formats.

Thank you.
Nicole

 

[theDBguy]

Not sure why you would be concerned about formatting. Typically, you would use the one the your business model requires. Unless, you're really talking about field data types rather than formatting.

 

[The_Doc_Man]

If you are staying with Access, just getting a new version of it, then don't plan to change anything. If you are using the 32-bit version of Access, stay with the newer 32-bit Access. Then you should have no mechanical need to change anything in a particular project. You still could have an aesthetic need for a change, but the version of Access won't affect aesthetic choices at all. And finally, there is a project-technical need for a change. Again, the version of Access won't affect the timing of that change either. If your problem has evolved due to requirements that have changed over time, don't let the version of Access stop you from doing what you need for continued project viability.

Now, have said that you should not consider a version change of Access as a REASON to make a change, it remains a possibility that you might wish to make changes because of new Access features that are now available. There is no way for us to know that kind of detail, but new features can be a good reason to make changes. There is, however, a potential pitfall if you still have some systems that won't be getting new versions and thus would be unable to support the new features. This becomes a case of AVOIDING change if your environment will not remain uniform, because in that case, you need the lowest common denominator, which is to say, "earliest version" of Access. A higher version of Access can open a project from a lower version, but the reverse isn't always true. I don't recall the details of your user base, but just remember that you cannot upgrade Access itself and then upgrade the features using the new Access unless ALL of your users can be on the new Access.

 

[GPGeorge]

We are getting ready to upgrade our MsAccess databases and would like to have advice on the formatting of the fields that we have been using for years.

Attached is the different formats in our MsAccess 2003 database(s) and I would appreciate suggestions on if and how I should change the field formats.

Thank you.
Nicole

Those formats appear to be pretty innocuous, which leads to the question, What problem would you be solving by changing any of those formats?

Keep in mind that formatting only affects the visual display to human eyes, not the underling value stored in the table. So if these display formats have worked fine for some time, what has changed to make them now problematic?

 

[plog]

1. Who cares about the formatting of data in a field? Tables are for holding data, reports and forms are for displaying data.

2. Why is Percent a text?

3. What quantity can be a decimal? Most inventory can only have whole numbers of items.

 

[Pat Hartman]

For your own sanity, NEVER use formats at the table level. All that does is to obfuscate the actual value the column contains. Just ask all those posters who have trouble working with dates when they make the mistake of populating them with Now() when they should have used Date() and then changed the format to "hide" the time component.

Format on forms and reports.

 

[access2010]

For your own sanity, NEVER use formats at the table level. All that does is to obfuscate the actual value the column contains. Just ask all those posters who have trouble working with dates when they make the mistake of populating them with Now() when they should have used Date() and then changed the format to "hide" the time component.

Format on forms and reports.

W.O.W, I never thought of this before.
Do you have an opinion on following Pat Hartman's advice and remove the formatting from the table and do the formatting on the forms and reports ? ? ?

​

 

[CJ_London]

As pat says, remove all formatting in tables. Existing forms and reports will have picked up the formatting from the fields when they were first created, so should not require any changes

 

[Minty]

Totally agree.
I've had all sorts of fun and games with legacy systems we've started to look after where formats applied at table level hide the true values being stored and then wondering why we were getting rounding errors.

As others have said - formatting should only be for display purposes.

 

[GPGeorge]

W.O.W, I never thought of this before.
Do you have an opinion on following Pat Hartman's advice and remove the formatting from the table and do the formatting on the forms and reports ? ? ?

​

Yes, of course. Think about the context.

Data is stored in tables. The application deals with the data and only deals with it on the basis of the underlying values stored in those tables. The application is not fooled or misled by superficial formatting. It knows the values stored for the data. When you attempt to apply formatting at that level, the potential for introducing ambiguity exists.

Humans interact with data in forms and reports. Humans are better able to grasp data when it has been formatted for display in ways with which we are familiar.

Formatting belongs only in those interface objects for the benefit of the humans who see it.

In code, there is no reason to format values either, but that context does have a difference from both the data in tables and the data in interface objects. That difference is that sometimes you may need to convert data, or manipulate it, to avoid ambiguity. Here, I'm primarily thinking of things like dates, where you might want to convert a date to a universal format, e.g. yyyymmdd, to avoid conflicts between US (MM/DD/YYYY) formats and the format used in most other regions of the world (DD/MM/YYYY). But that doesn't change the underlying values either. All it does is ensure unambiguous interpretations.

 

[Pat Hartman]

Microsoft designed Access to be "friendly" to inexperienced developers. To accomplish that goal, they closed their eyes and held their nose and implemented "features" that they would personally never use when developing an application. Formatting columns on tables is one of those "features" that just as frequently ends up as a "gotcha". If you format at the table, then the format gets propagated to forms/reports/queries. Sounds like it saves work, right. Not enough as far as I'm concerned. Since I bind forms to queries rather than binding them directly to tables, I can use the Format property of the column in the query and then the form will pick that up. So, I get the benefit of the propagation but not the gotcha's.