Is it right that "-" should be contained in the field names of MS Access ?

[John]

Hi, every body

I want to insert records into an Access table. One field name of the table
is "SD-1". My program will report a SQL syntax error, while there is no
error when I remove "-" from the field and form my program. Is that to say
: "-" should not be contained in the field names of MS Access ?

John

 

[Brendan Reynolds]

It's not an illegal character, but it can be mistaken for a minus sign.
'SELECT SD-1 FROM YourTable' is potentially ambiguous. It could mean 'select
the field named SD-1' or it could mean 'select the field named SD and deduct
1 from its value'. You can work around the problem by enclosing the field
name in square brackets in SQL statements and expressions ...

SELECT [SD-1] FROM YourTable

 

[Joan Wild]

According to http://support.microsoft.com/default.aspx?scid=kb;en-us;286335
(near the bottom of the page), the '-' should not be used in a field name.

Also see
http://groups.google.com/group/micr...523d7?lnk=st&q=&rnum=7&hl=en#d3b4184f799523d7

This bug was also reported in 2007, but won't be fixed.

 

[Granny Spitz via AccessMonster.com]

It's not an illegal character

Hon, if the name has to be surrounded with brackets to get it to work in SQL,
the name is either a reserved word or it has at least one illegal character
that Jet has to resolve in the Jet Expression Service. Since it's not a
reserved word and alphanumerics and the underscore aren't illegal characters,
we can narrow down which character in [SD-1] is illegal.

 

[Brendan Reynolds]

Perhaps I need to find a word other than 'illegal' to describe what I have
in mind.

There are certain characters that Access will not permit in field names, at
least not when creating those fields using the Access UI. (I have not tested
all the possible other methods of creating them). The hyphen is not one of
those characters. Here is a link to an on-line help topic that lists those
characters ...

http://office.microsoft.com/assistance/hfws.aspx?AssetID=HP051867691033

--
Brendan Reynolds
Access MVP

It's not an illegal character

Hon, if the name has to be surrounded with brackets to get it to work in
SQL,
the name is either a reserved word or it has at least one illegal
character
that Jet has to resolve in the Jet Expression Service. Since it's not a
reserved word and alphanumerics and the underscore aren't illegal
characters,
we can narrow down which character in [SD-1] is illegal.

 

[Granny Spitz via AccessMonster.com]

Perhaps I need to find a word other than 'illegal' to describe what I have
in mind.

How about differentiating between what's allowable in a column name? As in
"you're 'allowed' to use hyphens in column names," just like "you're
'allowed' to burn your money in your fireplace"? Hyphens aren't illegal, but
it's not advisable to use them in column names because you have to take
special precautions (add brackets and make sure it's never rewritten by Jet
in subqueries) or else you'll burn up time fixing broken queries (because
time = money).

 

[Brendan Reynolds]

As a general rule, I try to avoid telling people what to do. I try to draw
their attention to the consequences and let them draw their own conclusions.
(I'm sure I don't always succeed, but I try).

 

[Granny Spitz via AccessMonster.com]

As a general rule, I try to avoid telling people what to do. I try to draw
their attention to the consequences and let them draw their own conclusions.
(I'm sure I don't always succeed, but I try).

As a general rule, I find telling people "Watch out for that cow pattie!" and
leaving the rest to their imagination is a lot more effective than "Make sure
you wear shoes when you walk across the pasture" and letting them draw their
own conclusions. <g>

 

[Brendan Reynolds]

There are some fundamental differences between designing a database and
walking across a cow pasture. One of them is that it isn't necessary to be
very smart to go walking in a cow pasture. It is necessary to be smart to
successfully design databases. Smart people are capable of drawing their own
conclusions and generally, in my experience, welcome the opportunity to do
so.

Anyhow, the pleasure of walking barefoot in the pasture is worth the risk.
Just remember to wash your feet before you come into the house! Ya gotta
draw the line somewhere!

 

[John Vinson]

Anyhow, the pleasure of walking barefoot in the pasture is worth the risk.

Your pastures must have a lot less sharp stubble, cheatgrass seeds,
rocks and thistle than the ones around here....! <g>

John W. Vinson[MVP]

 

[Brendan Reynolds]

Your pastures must have a lot less sharp stubble, cheatgrass seeds,
rocks and thistle than the ones around here....! <g>

Admittedly, I would not recommend walking barefoot in fields around Dublin,
where I now live. I had in mind the fields of County Leitrim, which is where
my father is from. When they were children, my father and his sisters wore
shoes only on Sundays - and then only under protest.

 

[John]

Thank you all

John

 

[David F Cox]

Sometime we should run a contest to match the ends of threads with their
beginnings.

 

[Granny Spitz via AccessMonster.com]

There are some fundamental differences between designing a database and
walking across a cow pasture.

I'm sorry, my analogy isn't appropriate for database design. I wanted a
vivid image that stops people in their tracks and picturing something hot and
steamy squishing between your toes fit the bill. I didn't mean for you to
take me seriously, hon. My apologies.

 

[Brendan Reynolds]

Perhaps it might be better if we both dispense with analogies and stick to
the technical issue.

The on-line help topic at the following URL says: "Names of fields,
controls, and objects in Microsoft Access ... Can include any combination of
letters, numbers, spaces, and special characters except a period (.), an
exclamation point (!), an accent grave (`), and brackets([])."

http://office.microsoft.com/assistance/hfws.aspx?AssetID=HP051867691033

The KB article at the URL Joan posted, by contrast, includes the hyphen in a
list of characters which it says "must not be used as part of a field name
or as part of an object name".

http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

The Access UI will actively prevent the use of the characters in the first,
help topic list. It will not prevent the use of all of the characters in the
second, KB article list.

One of the characters that is listed in the KB article list but not in the
help topic list is the colon ). I am responsible for maintaining and
enhancing a number of Access applications that were not originally designed
by me. The designer of those databases habitually used not one, but two
colons in every field name. I am not permitted to change those field names.
Do they cause me problems? Yes. Would I change them if I were permitted to
do so? Yes. Can I work around the problems when I have to? Yes. Would I
prefer not to have to do so? Of course.

In my first post in this thread I stated that the hyphen 'could be mistaken
for a minus sign' and that its use in a field name was 'potentially
ambiguous and could cause the user to have to 'work around the problem'. You
later stated that its use in a field name was 'not advisable'. I agree that
the use of the hyphen - or any of the other characters listed in the KB
article - in a field name is not advisable. I hope the extended discussion,
even with its occasional diversion, has provided some clarification.

 

[Michael Gramelspacher]

Perhaps it might be better if we both dispense with analogies and stick to
the technical issue.

The on-line help topic at the following URL says: "Names of fields,
controls, and objects in Microsoft Access ... Can include any combination of
letters, numbers, spaces, and special characters except a period (.), an
exclamation point (!), an accent grave (`), and brackets([])."

http://office.microsoft.com/assistance/hfws.aspx?AssetID=HP051867691033

The KB article at the URL Joan posted, by contrast, includes the hyphen in a
list of characters which it says "must not be used as part of a field name
or as part of an object name".

http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

The Access UI will actively prevent the use of the characters in the first,
help topic list. It will not prevent the use of all of the characters in the
second, KB article list.

One of the characters that is listed in the KB article list but not in the
help topic list is the colon ). I am responsible for maintaining and
enhancing a number of Access applications that were not originally designed
by me. The designer of those databases habitually used not one, but two
colons in every field name. I am not permitted to change those field names.
Do they cause me problems? Yes. Would I change them if I were permitted to
do so? Yes. Can I work around the problems when I have to? Yes. Would I
prefer not to have to do so? Of course.

In my first post in this thread I stated that the hyphen 'could be mistaken
for a minus sign' and that its use in a field name was 'potentially
ambiguous and could cause the user to have to 'work around the problem'. You
later stated that its use in a field name was 'not advisable'. I agree that
the use of the hyphen - or any of the other characters listed in the KB
article - in a field name is not advisable. I hope the extended discussion,
even with its occasional diversion, has provided some clarification.

I do not know what importance ISO 11179-5 has in the Access
database world. This discusses Naming and Identification
Principles for Data Elements. It says that separators between
terms may have no particular meaning or have meaning. It uses
the example
Cost-Budget_Period-Total-Amount. In this example the hyphen
has meaning as a word separator in terms. The underscore
separates terms.

ISO does not prescribe this naming rule. It merely allows this
naming rule. Since Access allows a hyphen in column names, a
person or an organization is free to adopt such a rule. My
reading of this is that the important thing is that naming
rules should be established and be consistent within an
organization.

 

[Brendan Reynolds]

I do not know what importance ISO 11179-5 has in the Access
database world. This discusses Naming and Identification
Principles for Data Elements. It says that separators between
terms may have no particular meaning or have meaning. It uses
the example
Cost-Budget_Period-Total-Amount. In this example the hyphen
has meaning as a word separator in terms. The underscore
separates terms.

ISO does not prescribe this naming rule. It merely allows this
naming rule. Since Access allows a hyphen in column names, a
person or an organization is free to adopt such a rule. My
reading of this is that the important thing is that naming
rules should be established and be consistent within an
organization.

If I may borrow Granny's term, I would suggest that it would be ' not
advisable' to do so.

 

[Brendan Reynolds]

If I may borrow Granny's term, I would suggest that it would be ' not
advisable' to do so.

Oops. That was a bit ambiguous, wasn't it? I mean that it would be 'not
advisable' to adopt a rule that required or encouraged hyphens in field
names.

Standards are, generally speaking, a good thing, but some consideration has
to be given to whether the tools we intend to use support the standard.

 

[Tony Toews]

I want to insert records into an Access table. One field name of the table
is "SD-1". My program will report a SQL syntax error, while there is no
error when I remove "-" from the field and form my program. Is that to say
: "-" should not be contained in the field names of MS Access ?

You may wish to change the field name.

See the Scan and replace utilities section at the Microsoft Access
third party utilities, products, tools, modules, etc. page at my
website.
http://www.granite.ab.ca/access/thirdparty.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Tony Toews]

You may wish to change the field name.

Also note that if you have a split application, with a Front End and
Back End you will need to change the field name in the backend
manually. Then do the find and replace in the front end.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm