Field names with spaces in Append Queries

[gblack]

OK this one isn't vital or anything, I was just curious...

Inevitably I run across a field that has spaces in the heading. E.G. Field 1. Recently I have done so with a huge table filled with these types of field names (i.e. field names with spaces).

When I drag and drop these fields down ina query, Access seems to recognize the field names with spaces and doesn't put brackets around these values by default in the GUI... however, if I click the SQL statement that is made, all the field names with spaces appear with brackets around them and the ones that don't have spaces do not.

The kicker is that if you now change this from a SELECT query to an APPEND query, when you choose the table to append to... Access will auto fill all the fields with the same names, put brackets around the fields that have spaces.

Then when you try to run the append query Access throws this error:

The INSERT INTO statement contains the following field name:'[Field 1]' Make sure you have typed the name correctly, and try the operation again.

When you look at the SQL, though, everything that should have brackets does... so how do you fix it?

The answer is to remove the brackets in the GUI, but this is very time consuming if you have a table with a ton of "spaced field names".

The crazy thing is that once you remove the brackets in the GUI, Access remembers that you've deleted them. But where? How? Even if I remove the brackets in the GUI, if I then look in the SQL the brackets are there for both the INSERT INTO and the SELECT statement... So how does Access know, to leave the brackets off, at the append line, once you've deleted them?

Because I inherited a GIANT table filled with these types of field names, whenever I create an append table, I have to go through and delete all the brackets by hand... this is uber-lame!

Does anyone know of an easier way to deal with this? Or how I can get Access to force brackets initially, for field names with spaces. Seems that'd be a setting or something... why would they force brakctes for the instert but not for the select? It doesn't make any sense. Seems like if that throws an error they'd want to keep it consistent.

Anyway, if you have some insight as to the best way to deal with this, I'd be please to learn something new... I know what some of you might say... but please don't tell me not to use spaces in field names... that won't be helpful as I already know not to do this... like I said, sometimes the rest of the world doesn't know it's a no no

Thanks,
Gary

 

[Galaxiom]

Usually a problem with a query not recognising a valid field name is due to corruption caused by Name Auto Correct.

Have you renamed any of the fields in the destination table with Name AutoCorrect turned on?

 

[gblack]

Hmm... Not sure... I may have... but are you telling me that you can open a query based on a table which has spaces in the field name... turn it into an append query, then choose a table with the same columns with spaces in them... let access autmatically pull them up... and you have no issues? Give it a shot and see if you don't run into the same issue...

It could just be what you said... though... I will try in another file. Soon as this process I am running gets finished.

 

[Galaxiom]

Interesting. I have reproduced the exact behaviour you describe in Access 2007. I would call it a bug.

It only happens with a Select query already having a field included and then changing the query type. If the query is converted to an append type before the field is added then the query is correctly constructed.

The only difference is the brackets around the fieldname in the design view of the bad query. The SQL is identical in both the good and bad queries.

Most professional developers would not have come accross this because we don't use spaces in names.

 

[Galaxiom]

The good news it is easily fixed.
Backup you database, put this Sub into a Standard Module and run it.

Private Sub FixSQL()
 
Dim db As DAO.Database
Dim qdf As QueryDef
 
   Set db = CurrentDb
 
   For Each qdf In db.QueryDefs
       qdf.SQL = qdf.SQL
   Next
 
End Sub

It is a loop that simply rewrites the SQL of all querydefs forcing them to be reconstructed from the SQL rather than the Design View.

Surprisingly the querydefs don't even need to be saved.

 

[Galaxiom]

BTW. You can create your queries and convert them to append before you add the fields. When you want to review what the query is going to insert simply choose the Datasheet View. This will show the results without inserting them.

 

[Galaxiom]

If you just want to fix a particular query then copy this into the Immediate Window and press return.

Set db = Currentdb: db.QueryDefs("QueryName").SQL = db.QueryDefs("QueryName").SQL

(Change QueryName to the name of the query you want to fix.)

 

[Simon_MT]

Another method that might help is to save your Append Query and re-edit it. Yes, I too think it is a bug but I don't often have to create or have to manipulate - so I put this down to an irration rather than a showstopper.

Simon