Access fieldnames contain spaces (RESOLVED)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Access fieldnames contain spaces (RESOLVED)

Author

Topic

RBianca
Starting Member

10 Posts

Posted - 2008-03-06 : 16:11:44

Help! I need to write a SQL statement to INSERT a record into an Access table, which has an Autonumber field as its primary key. Sadly, many of the fieldnames contain one or more spaces in the name (eg. "field name").

When I try to write the insert statement with the fieldnames specified like this:
INSERT INTO tblOne ( fieldname1, fieldname2, field name 3 )
VALUES ( value1, value2, value3 )
the spaces in the last fieldname cause a problem.

When I try encasing the problem fieldname in brackets, like this:
INSERT INTO tblOne ( fieldname1, fieldname2, [field name 3] )
VALUES ( value1, value2, value3 )
SQL thinks I want to be prompted for a value.

When I try to use the fully-qualified name in the insert statement like this:
INSERT INTO tblOne ( fieldname1, fieldname2, tblOne.[fieldname 3] )
VALUES ( value1, value2, value3 )
I get a SQL syntax error.

Then I tried doing the insert without spelling out the fieldnames at all, like this:
INSERT INTO tblOne
VALUES ( value1, value2, value3 )
but I am required to provide a value for every field, including the autonumber field, which defeats the purpose of the autonumber.

Any ideas?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-06 : 16:23:52

What is the syntax error?

is it [field name 3] or [fieldname 3]

Obviously you have to wrap the field name in brackets if there are spaces in the field names.

Poor planning on your part does not constitute an emergency on my part.

RBianca
Starting Member

10 Posts

Posted - 2008-03-06 : 16:27:01

I realize that you need to wrap field names in brackets when there are embedded spaces. However, the INSERT INTO statement translates that into a prompt for data.

PS - [field name 3] is just an example. These are not real field names.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-06 : 16:33:02

that only happens when the bracketed column name does not exist.

If you mistyped the name (and like I said, is it [field name 3] or [fieldname 3])...then you would get prompted as if the text inside the brackets was a parameter prompt and not a column. Make sure the column is spelled correctly.

Qualify the table / field using the full syntax (in access it is ! instead of .)

[TableName]![Field Name]

Poor planning on your part does not constitute an emergency on my part.

RBianca
Starting Member

10 Posts

Posted - 2008-03-06 : 16:42:05

I wish it were that easy. I tried both dot and bang. The fieldname is just not recognized. This table contains 48 fields, all given names like book titles. I have tried updating only a few fields at a time, in case it was just a typo, but surely not 48 typos...

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-03-06 : 16:50:34

If you don't give us specifics, we cannot help you. All we can tell you is that if a column name has spaces, you delimit it with [ and ]. That's it. It will work if you spell your column names correctly -- including all spaces. If you cannot get it to work, you need to post actual code and the actual table(s) you are querying with their actual column names and we can take a look.

After all, if I tell you I am spelling the name of the street I live on wrong, can you help me troubleshoot how to spell it correctly if you don't know where I live and if you don't know the actual address? Probably not, right? That's what we are dealing with here.

- Jeff
http://weblogs.sqlteam.com/JeffS

RBianca
Starting Member

10 Posts

Posted - 2008-03-06 : 17:13:06

Run-time error '3127':

The INSERT INTO statement contains the following unknown field
name: 'tblProduct_Codes!Confirm Fee'. Make sure you
have typed the name correctly, and try the operation again.

Here is a simplified example of the SQL statement generated:
INSERT INTO tblProduct_Codes ( Product_Code, Division_ID, Internal_Company_ID, [tblProduct_Codes]![Confirm Fee] ) VALUES ( '888888', 1, 15, '35325' )

I have tried substituting "Confirm Fee" with any of four dozen other fields with equally frustrating names. The same message comes back. The field name DEFINITELY exists in the table.
SELECT statements do not cause this problem, only INSERT statements.

If I write the INSERT INTO statement without specifying the field names at all, I run into trouble with the autonumber field. SQL is looking for a value for EVERY field, including the autonumber field, and won't automatically provide the next number. Can you think of a way around that?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-06 : 17:30:00

The site was down while i was trying to edit this

I copied your syntax and made a bogus table with your values and field names.

Using your syntax, I actually WAS able to reproduce the error (using INSERT VALUES()

Using this syntax I didn't


INSERT INTO tblProduct_Codes ( Product_Code, Division_ID, Internal_Company_ID, [Confirm Fee] )
SELECT tblProduct_Codes.Product_Code, tblProduct_Codes.Division_ID, tblProduct_Codes.Internal_Company_ID, tblProduct_Codes.[Confirm Fee]
FROM tblProduct_Codes;

and likewise no error here:


INSERT INTO tblProduct_Codes ( Product_Code, Division_ID, Internal_Company_ID, [Confirm Fee] )
SELECT '888888', 1, 15, '35325' ;

I don't get the error.

Do Insert / Select rather than Insert (Values)

Poor planning on your part does not constitute an emergency on my part.

RBianca
Starting Member

10 Posts

Posted - 2008-03-07 : 10:09:47

OMG - IT WORKS!!!!

Thank you thank you, dataguru1971, for sticking with me! I was beginning to think I was finished.

=)

mullobird
Starting Member

1 Post

Posted - 2009-04-10 : 10:41:04

I am trying to execute the following query in MS Access 2007.

INSERT INTO [Global Standard Rate Card Tbl] ( [Territory], [Rate Card Name], [Product Type], [Agency], [Wholesale], [Tier Code], [Product Line], [Active], [Currency], [Track Type] )
SELECT [Belgium Standard Rate Card].[Territory], [Belgium Standard Rate Card].[Rate Card Name], [Belgium Standard Rate Card].[Product Type], [Belgium Standard Rate Card].[Agency], [Belgium Standard Rate Card].[Wholesale], [Belgium Standard Rate Card].[Tier Code], [Belgium Standard Rate Card].[Product Line], [Belgium Standard Rate Card].[Active], [Belgium Standard Rate Card].[Currency], [Belgium Standard Rate Card].[Track Type]
FROM [Belgium Standard Rate Card];

Whenever I run the query, I get the following error:

The INSERT INTO statement contains the following unknown field name: <field name>. Make sure you have typed the name correctly, and try the operation again.(Error 3127)

I looked at a couple of posts regarding this same error, but I don't seem to be doing anything wrong in my query. I created the query using Access's query design feature. Let me know if anyone can help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources