Table formatting best practices

[deko]

I've heard it's best not to have any formatting specified for Table fields
(except perhaps Currency), and instead set the formatting in the Form or
Report. But what about Yes/No fields? When I create a Yes/No field in a
Table the default format is "Yes/No". If I clear or change the Format
property in the General Tab (and keep the default "Checkbox" in the Lookup
tab), the field still looks the same when I open the table in Datasheet
view.

I have all kinds of queries and code that get and set the value of these
Yes/No fields using "-1" for True and "0" for False. Does it make any
difference what the Table Format setting is? What about the Display Control
setting on the Lookup tab?

I recently experienced an unnerving error that was apparently caused by an
aberrant Yes/No field:

"Error Number 3343: Unrecognized database format
'C:\MyDirectory\MyAccess2003.mdb'.

I'm still in search of an explanation and wondering if the Table formatting
could be an issue.

Thanks in advance.

[Allen Browne]

Lookup fields obfuscate what is actually stored in the table, create
spurious indexes, and introduce other problems as discussed at:
http://www.mvps.org/access/lookupfields.htm

It is not the Display Control that causes the problems, so use it as you
wish.

Similary, the Format property of a field in the table is not evil. Not only
does it show in a query, but the controls on a new form/report inherit these
properties. If helps you then use it.

The problems I am aware of with the Format property are:
1. Don't use on Memo fields: it truncates them to 255 characters.

2. Don't use to force the display of text to upper/lower case. The data
doesn't match if you use case-sensitive searches, and users think the data
is wrong when exported.

3. Don't set to Short Date to try to hide the time component in date fields,
typically because =Now() was used as Default Value when it should have been
=Date().

4. It can make it more involved to modify a back end if you want to change
the formatting in an update of a database distributed to many locations
(i.e. where changing the front end is not enough).

AFAIK, the Format property does not contribute to corruption. For a list of
things that do, see:
Preventing Corruption
at:
http://members.iinet.net.au/~allenbrowne/ser-25.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"deko" <www.clearpointsystems.com@use_contact_form.com> wrote in message
news:sk*****************@newssvr14.news.prodigy.co m...

I've heard it's best not to have any formatting specified for Table fields
(except perhaps Currency), and instead set the formatting in the Form or
Report. But what about Yes/No fields? When I create a Yes/No field in a
Table the default format is "Yes/No". If I clear or change the Format
property in the General Tab (and keep the default "Checkbox" in the Lookup
tab), the field still looks the same when I open the table in Datasheet
view.

I have all kinds of queries and code that get and set the value of these
Yes/No fields using "-1" for True and "0" for False. Does it make any
difference what the Table Format setting is? What about the Display
Control
setting on the Lookup tab?

I recently experienced an unnerving error that was apparently caused by an
aberrant Yes/No field:

"Error Number 3343: Unrecognized database format
'C:\MyDirectory\MyAccess2003.mdb'.

I'm still in search of an explanation and wondering if the Table
formatting
could be an issue.

Thanks in advance.

[deko]

> Lookup fields obfuscate what is actually stored in the table, create

spurious indexes, and introduce other problems as discussed at:
I didn't know what a Lookup field was until I read about it here:
http://support.microsoft.com/?kbid=304463

It sounds like you have to set the Display Control to Combo Box on the
Lookup tab of Field Properties when the table is in Design View. I've never
done that certialnlly don't plan to after reading about the evils of Lookup
fields!
It is not the Display Control that causes the problems, so use it as you
wish.

Similary, the Format property of a field in the table is not evil. Not only does it show in a query, but the controls on a new form/report inherit these properties. If helps you then use it.
If it doesn't matter, then I will standardize all my table fields as having
no format, and will always use "Text Box" for Display Control. This makes
Yes/No fields appear as "0" and "-1" which I think is easier to work with.
The problems I am aware of with the Format property are:
1. Don't use on Memo fields: it truncates them to 255 characters.

2. Don't use to force the display of text to upper/lower case. The data
doesn't match if you use case-sensitive searches, and users think the data
is wrong when exported.

3. Don't set to Short Date to try to hide the time component in date fields, typically because =Now() was used as Default Value when it should have been =Date().

4. It can make it more involved to modify a back end if you want to change
the formatting in an update of a database distributed to many locations
(i.e. where changing the front end is not enough).
Thanks for the heads up. With my new standard I shouldn't run into any of
these problems.
AFAIK, the Format property does not contribute to corruption. For a list of things that do, see:
Preventing Corruption
at:
http://members.iinet.net.au/~allenbrowne/ser-25.html

What I found interesting here is that you say a potential cause of
corruption is "Sharing a single mdb file amongst users". Is sharing a
single mdb really that bad? I'm wondering if opening the same mdb twice -
so there are two instances of the same database open at the same time on the
same machine - could have contributed to my "3343 Unrecognized database
format" error. Is there a way to prevent a second instance from opening on
the same machine?

Thanks for the help.

[Allen Browne]

Answered embedded.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"deko" <www.clearpointsystems.com@use_contact_form.com> replied in message
news:Ux*****************@newssvr21.news.prodigy.co m...

What I found interesting here is that you say a potential cause of
corruption is "Sharing a single mdb file amongst users". Is sharing a
single mdb really that bad? I'm wondering if opening the same mdb twice -
so there are two instances of the same database open at the same time on
the
same machine - could have contributed to my "3343 Unrecognized database
format" error.
Yep: that can cause the same issue.
Is there a way to prevent a second instance from opening on
the same machine?

See:
Preventing multiple instances of a database
at:
http://www.mvps.org/access/api/api0041.htm

I don't do that personally, but you could if this is an issue for you.

[deko]

> > Is there a way to prevent a second instance from opening on

the same machine?
See:
Preventing multiple instances of a database
at:
http://www.mvps.org/access/api/api0041.htm

Just for grins, I tried setting the default open mode to Exclusive in Tools Options > Advanced (before adding the function), but a second instance

still opened. In any case, that function seems to do the trick. I'm
calling it on Form_Open of the default startup form - is this the best place
to call it?

I also came across some of your recommendations at:

http://members.iinet.net.au/~allenbrowne/bug-09.html

Good stuff. I used the FixZLS function.