Hi, Gerry.
We have a field name called
[Addres1&2].
Not a good idea. This name contains an illegal character. Placing brackets
around a bad name will often "fix" it for Jet or VBA code, but there is no
guarantee that this Band-Aid will always work. As a rule of thumb, if the
character isn't alphanumeric or the underscore, then it's _always_ illegal.
Don't use illegal characters in names of objects, variables, or procedures,
and you won't encounter bugs like these.
If we do open it, Access 2003 adds a space between to make it: [Addres1 & 2]
and our query is now broken.
This is one of those places where the brackets don't work. There are more.
I hope we do not have to rename this field as it is used in 97 databases on
about 97 different tables.
Of course you don't have to. You can:
1.) Keep the databases in Access 97, instead of converting to a more recent
version of Access; or
2.) Not open this query in Design View. If you do, then open the query in
SQL View and change the name to its original, then save it and run the query.
If you want my advice, I'd find the developer of these databases and tell
him he's going to have to change every occurrence of illegal characters in
every one of the databases, because he's the one who broke them. After he's
had the heart attack, give him a tool like Speed Ferret to make this ordeal a
snap. (Okay. 97 snaps.)
http://www.speedferret.com/speedferret.html
HTH.
Gunny
See
http://www.QBuilt.com for all your database needs.
See
http://www.Access.QBuilt.com for Microsoft Access tips.
(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
Gerry b said:
We have converted an Access 97 db to 2003. We have a field name called
[Addres1&2].
Our Append query will run as long as we do not open it in Design View after
conversion.
If we do open it, Access 2003 adds a space between to make it: [Addres1 & 2]
and our query is now broken. We get the error message:
"The INSERT INTO statement contains the following unknown field name:
'Adress1 & 2'. Make sure you have typed the name correctly, and try the
operation again."
I hope we do not have to rename this field as it is used in 97 databases on
about 97 different tables. HELP! HELP! HELP! I don't know what to do!
Any suggestions would be greatly appreciated,
Thanx,
Gerry b.
