Access 2007 Append Query – Issue with automatically inserted brack

I ran into something strange while doing a Make Table Query that was followed
by an Append Query (from a “purchased system� table that has many field names
with embedded blanks.)

To understand this issue better, here is what I did to recreate the problem.

Created a new table with only one field named “Cust Name� (note the embedded
space in the field name)

Used an Access “Make Table� query – this worked nicely.

Then I tried an Access “Append Query�. Access generated this field name in
the Append To: field [Cust Name] (Note the brackets that Access
inserted)

When I run it, I receive the following error msg.
“The INSERT INTO statement contains the following unknown field name: ‘[Cust
Name]�. Make sure you have typed the name correctly, and try the operation
again�

I can resolve this by removing the brackets.

This is not a big deal for this little test, but the original production
table has many fields with embedded spaces in the field names. It would be
nice if I could somehow ask Access to not wrapper these field names with
brackets when doing an Append Query.

Is this a feature? Am I missing something obvious?

Thanks,
Brad

Field names with space must have brackets (use underscore instead).
Post your complete query SQL for both queries.

--
Build a little, test a little.


"Brad" wrote:

I ran into something strange while doing a Make Table Query that was followed
by an Append Query (from a “purchased system� table that has many field names
with embedded blanks.)

To understand this issue better, here is what I did to recreate the problem.

Created a new table with only one field named “Cust Name� (note the embedded
space in the field name)

Used an Access “Make Table� query – this worked nicely.

Then I tried an Access “Append Query�. Access generated this field name in
the Append To: field [Cust Name] (Note the brackets that Access
inserted)

When I run it, I receive the following error msg.
“The INSERT INTO statement contains the following unknown field name: ‘[Cust
Name]�. Make sure you have typed the name correctly, and try the operation
again�

I can resolve this by removing the brackets.

This is not a big deal for this little test, but the original production
table has many fields with embedded spaces in the field names. It would be
nice if I could somehow ask Access to not wrapper these field names with
brackets when doing an Append Query.

Is this a feature? Am I missing something obvious?

Thanks,
Brad

Brad wrote:

I ran into something strange while doing a Make Table Query that was followed
by an Append Query (from a “purchased system” table that has many field names
with embedded blanks.)

To understand this issue better, here is what I did to recreate the problem.

Created a new table with only one field named “Cust Name” (note the embedded
space in the field name)

Used an Access “Make Table” query – this worked nicely.

Then I tried an Access “Append Query”. Access generated this field name in
the Append To: field [Cust Name] (Note the brackets that Access
inserted)

When I run it, I receive the following error msg.
“The INSERT INTO statement contains the following unknown field name: ‘[Cust
Name]”. Make sure you have typed the name correctly, and try the operation
again”

I can resolve this by removing the brackets.

This is not a big deal for this little test, but the original production
table has many fields with embedded spaces in the field names. It would be
nice if I could somehow ask Access to not wrapper these field names with
brackets when doing an Append Query.

Is this a feature? Am I missing something obvious?


The thing you are missing is that the [ ] are standard name
brackets. They are required if a name includes a non
alphanumeric/underscore character and are optional when the
name starts with a letter and contains only
alpanumeric/underscoare characters. There are many places
where Access inserts them, even when they are not required.
I have never heard of them causing a problem unless the are
usind in a subquery in a FROM clause.

I suspect there is more going on than you have speculated.
To see all the details about a query, you have to switch to
SQL view where you can see the real query instead of the
query designer's user interface.

There may even be something funny in the SELECT INTO query
that is getting in the way of the INSERT INTO query so
double check both queries in SQL view.

--
Marsh
MVP [MS Access]