A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Access 2007 Append Query – Issue with automatically inserted brack



 
 
Thread Tools Display Modes
  #1  
Old May 18th, 2010, 07:48 PM posted to microsoft.public.access.queries
Brad
external usenet poster
 
Posts: 943
Default 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





  #2  
Old May 18th, 2010, 08:41 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Access 2007 Append Query – Issue with automatically inserted brack

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





  #3  
Old May 18th, 2010, 09:03 PM posted to microsoft.public.access.queries
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Access 2007 Append Query Issue with automatically inserted brack

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]
  #4  
Old May 19th, 2010, 06:12 PM posted to microsoft.public.access.queries
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Access 2007 Append Query Issue with automatically inserted b

Brad wrote:
I can recreate the problem with these steps (takes about 2 minutes, if you
want to experiment)

Create new Access 2007 database
Create Table1 with one field called Customer Name
Add one record to this table

Create Query1 with Query Design
Pull the Customer Name field into Query1
Choose Make Table query (New table name = Table2)
Run this (From the Access Design panel Big Red Exclamation Point)
This creates Table2 nicely so far, so good

Create Query2 with Query Design
Choose Table1
Pull the Customer Name field into Query2
Choose Append for this query
Choose Table2 for the Append To Table Name

Access now puts [Customer Name] in the Append To: field

I then push Run and get this message
The INSERT INTO statement contains the following unknown field name :
[Customer Name]. Make sure you have typed the name correctly, and try the
operation again.

If I manually remove the brackets in the Append To: field, the insert will
work.

I am not changing any SQL via the SQL-View.

I can get around this issue, but I am curious why this is happening.

Thanks
Brad

PS. Here is the underlying SQL that Access 2007 has generated

*** Query1 (Make Table)
SELECT Table1.[Customer Name] INTO Table2
FROM Table1;

*** Query2 (Append)
INSERT INTO Table2 ( [Customer Name] )
SELECT Table1.[Customer Name]
FROM Table1;



My Access 2007 machine was wiped for other uses so I can't
try it there. I'll have to take your word for what happens
there.

I guess this is a place where Access 2007 adds the [ ] when
it's not appropriate. In A2003 when I tried this in the
query designer, Access did not add the [ ] and if I added
them myself, Access removed them. Access 2010 did not add
the [ ] either. Regardless, I ended up with the same SQL
view you have and it ran fine.

This is another good reason to never use names that require
[ ]. I don't do that or I worked in SQL view where the [ ]
are under my control so I've never seen this particular
problem.

I think your best action at this point is to rename the
field without the space. If you do that, I believe you will
be a lot happier in the future.

--
Marsh
MVP [MS Access]
  #5  
Old May 19th, 2010, 07:39 PM posted to microsoft.public.access.queries
Brad
external usenet poster
 
Posts: 943
Default Access 2007 Append Query – Issue with automatically inserted b

Marsh,

Thanks for the help.

I agree, it is best to avoid embedded spaces in field names.

Because the fields names in this situartion are being established in another
system, we do not have control over them.


I did some more experimenting with this issue.

If I follow these steps, Access 2007 inserts the brackets and the problem
occurs.

1. Create Query2 with “Query Design”
2. Choose Table1
3. Pull the “Customer Name” field into Query2
4. Choose “Append” for this query
5. Choose Table2 for the “Append To Table Name”

If I switch steps 3 and 4, the problem does not happen.

I believe that I learned something new today!

Thanks again for your help.

Brad


"Marshall Barton" wrote:

Brad wrote:
I can recreate the problem with these steps (takes about 2 minutes, if you
want to experiment)

Create new Access 2007 database
Create Table1 with one field called “Customer Name”
Add one record to this table

Create Query1 with “Query Design”
Pull the “Customer Name” field into Query1
Choose “Make Table” query (New table name = Table2)
Run this (From the Access Design panel – Big Red Exclamation Point)
This creates Table2 nicely – so far, so good

Create Query2 with “Query Design”
Choose Table1
Pull the “Customer Name” field into Query2
Choose “Append” for this query
Choose Table2 for the “Append To Table Name”

Access now puts [Customer Name] in the Append To: field

I then push Run and get this message
“The INSERT INTO statement contains the following unknown field name :
‘[Customer Name]’. Make sure you have typed the name correctly, and try the
operation again.”

If I manually remove the brackets in the “Append To: field, the insert will
work.

I am not changing any SQL via the SQL-View.

I can get around this issue, but I am curious why this is happening.

Thanks
Brad

PS. Here is the underlying SQL that Access 2007 has generated

*** Query1 (Make Table)
SELECT Table1.[Customer Name] INTO Table2
FROM Table1;

*** Query2 (Append)
INSERT INTO Table2 ( [Customer Name] )
SELECT Table1.[Customer Name]
FROM Table1;



My Access 2007 machine was wiped for other uses so I can't
try it there. I'll have to take your word for what happens
there.

I guess this is a place where Access 2007 adds the [ ] when
it's not appropriate. In A2003 when I tried this in the
query designer, Access did not add the [ ] and if I added
them myself, Access removed them. Access 2010 did not add
the [ ] either. Regardless, I ended up with the same SQL
view you have and it ran fine.

This is another good reason to never use names that require
[ ]. I don't do that or I worked in SQL view where the [ ]
are under my control so I've never seen this particular
problem.

I think your best action at this point is to rename the
field without the space. If you do that, I believe you will
be a lot happier in the future.

--
Marsh
MVP [MS Access]
.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:02 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 OfficeFrustration.
The comments are property of their posters.