Prev: Concatenate Text Fields to Date Field
Next: in access, round up to nearest fraction(.5)
From: Marshall Barton on 19 May 2010 13:12
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]
 | 
Pages: 1
Prev: Concatenate Text Fields to Date Field
Next: in access, round up to nearest fraction(.5)