RE: Using an & in [Append To:] Field

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" wrote:

> 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.
>
.

Relevant Pages

  • Re: Want help with database diagnostics tool design.
    ... Filter 2 Query ... what kind of databases you are trying to diagnose. ... any complicated n-tier solutions to this kind of application. ...
    (comp.lang.pascal.delphi.databases)
  • Re: Opening table - query running?
    ... That's actually the way relational databases, not just Access, ... unless you impose order by using a query with an order by clause. ... the rows will be returned in primary key sequence because Jet ...
    (microsoft.public.access.tablesdbdesign)
  • Many to Many Relationships
    ... The result of the query will be ... I am new to Access databases and to date ... Quotes and Jobs both have sub ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Code works in several databases except one
    ... databases that i use it in without any problems. ... There is a refresh button that loads the query list into a list ... Dim obj, dbs As Object ... Dim SQLStr, QuerySource As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: crosstab query parameter error
    ... If this is for your crosstab query, ... Reserved words or contain illegal characters to try to slide them past Jet ...
    (microsoft.public.access.queries)