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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|