Append query unknown field name error, views but doesn't run...

A

Accessor

Hi all. I'm trying to fix an append query that generates the right recordset
just fine, but on run I get the error: "The INSERT INTO statement contains
the following unkown field name: '[CompanyDescription]'. Make sure you have
typed the name correctly, and try the operation again." I got this error with
that field under a different name (same name both tables.) Here's the sql:

INSERT INTO tblSymbolsMain ( Symbol, [CompanyDescription], [Security Type] )
SELECT qryNewSymbolsToAddToMainSymbols.Symbol,
qryNewSymbolsToAddToMainSymbols.CompanyDescription,
qryNewSymbolsToAddToMainSymbols.[Security Type]
FROM qryNewSymbolsToAddToMainSymbols;

So the field names match in both tables, the source is a find unmatched
query, and all field types are text.

Any ideas?? Thanks in advance...

Kevin
 
J

Jerry Whittle

I would check very carefully to see if it's [CompanyDescription] or [Company
Description] (with a space) in both tables. Usually you don't automatically
get the [] brackets unless there's a space. Both must match exactly or use an
alias.
 
A

Accessor

Hi Jerry, they're the same, just as I pasted it in. I got the error with a
different field name(s), in which there was a space. I removed it, but the
error persists.

Jerry Whittle said:
I would check very carefully to see if it's [CompanyDescription] or [Company
Description] (with a space) in both tables. Usually you don't automatically
get the [] brackets unless there's a space. Both must match exactly or use an
alias.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Accessor said:
Hi all. I'm trying to fix an append query that generates the right recordset
just fine, but on run I get the error: "The INSERT INTO statement contains
the following unkown field name: '[CompanyDescription]'. Make sure you have
typed the name correctly, and try the operation again." I got this error with
that field under a different name (same name both tables.) Here's the sql:

INSERT INTO tblSymbolsMain ( Symbol, [CompanyDescription], [Security Type] )
SELECT qryNewSymbolsToAddToMainSymbols.Symbol,
qryNewSymbolsToAddToMainSymbols.CompanyDescription,
qryNewSymbolsToAddToMainSymbols.[Security Type]
FROM qryNewSymbolsToAddToMainSymbols;

So the field names match in both tables, the source is a find unmatched
query, and all field types are text.

Any ideas?? Thanks in advance...

Kevin
 
J

John W. Vinson

Hi Jerry, they're the same, just as I pasted it in. I got the error with a
different field name(s), in which there was a space. I removed it, but the
error persists.

Removing the space changes the name. [Customer Name] and [CustomerName] and
[Customer_Name] are, to Access, three completely different and unrelated
fieldnames. They must match byte for byte, and a space is a character just
like any other character in this regard!

Are you certain that the fieldnames are in fact EXACTLY the same in the two
tables?
 
A

Accessor

I was just saying how they indeed are. I changed them yet again, and the
error persisted. I copied the table, renamed the fields again, rewrote the
query, and now it works. Thanks though... on to my next problem, running an
append query from excel vba...

John W. Vinson said:
Hi Jerry, they're the same, just as I pasted it in. I got the error with a
different field name(s), in which there was a space. I removed it, but the
error persists.

Removing the space changes the name. [Customer Name] and [CustomerName] and
[Customer_Name] are, to Access, three completely different and unrelated
fieldnames. They must match byte for byte, and a space is a character just
like any other character in this regard!

Are you certain that the fieldnames are in fact EXACTLY the same in the two
tables?
 
Joined
Jan 14, 2010
Messages
1
Reaction score
0
ACESS Issues with Append Queries

I have, a number of times, run into this problem with append queries in ACCESS using their query designer. The issue is that the program renames one of the fields in the query. I know for a fact that it renames the field because I have triple checked it, copied the field name from the database definition, and re-initiated the field in the Designer. The query will sometimes work once only to cough up the error on subsequent runs. I am asking Microsoft to work this bug out as I have databases with large numbers of entries on which I need to run queries, reports, and other database functions and if this problem exists how can I assure my supervisors that everything works as it is supposed to? This is critical data that is used for analysis and if one type of query doesn't work then why should we believe any other type will work as it is supposed to?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top