Prev: Help with expression builder in a Query to retrieve wanted dates
Next: 90 days and the first of the month
From: Marshall Barton on 18 May 2010 16:03
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]
 | 
Pages: 1
Prev: Help with expression builder in a Query to retrieve wanted dates
Next: 90 days and the first of the month