Access Append query deleting blank spaces in hyphenated column names ex: [Territory - Desc]

Can you paste the SQL for your query here?
J

Using spaces or characters other than the underscore forces you to use square brackets [ ] surrounding your object names, and is generally considered bad design.  

Here's an article that will help:  Leszynski/Reddick Naming Convensions...
http://www.mvps.org/access/general/gen0012.htm

Some of this is style.  What's more important is that you pick a naming convention and stick with it throughout your applciation.

Hope this helps.
-Jim

Jim,

The db is full of bad convention.  I didn't design it, just inherited it.  The strange thing here is that sometimes it works, some times it doesn't.  Rather than having me send you the query, try this out yourself, it's a simple excercise that will boggle the mind:

In Access 2003, create two tables.  In both tables, create two text columns, name them

Territory - Desc
Ship to - City

Sure, I hate the spaces and hyphens too, but bear with me.  Now, in one table, put a coulple of records

               Territory - Desc           Ship to - City
rec 1           mine                            here
rec 2           yours                           there

Now use the query designer to create an append query.  Try to append the two records from the first table to the second.  Save your query after you create it.  Then open the query and you'll see that the spaces have been removed in [Territory - Desc] but remain in [Ship to - City].  

What's up with that?

Hello tntmoeller

I could not recreate your problem. For example, the following query runs fine and gets saved without problem...

    INSERT INTO [Table - Test] ( [Territory - Desc], [Ship to - City] )
    SELECT [Table - Test].[Territory - Desc], [Table - Test].[Ship to - City]
    FROM [Table - Test];

As long as you keep the square brackets intact at all times, this works.

However, the one field that seems to create problems has one thing special: an SQL reserved word as part of the name. DESC is used to sort records in descending order and cannot be used as a field name, without square brackets that is.

Could you explain how you create the append query and show us the resulting modified SQL?

(°v°)

Jim,

I'm sure it has to do with MS doing some auto-formatting.  The [Ship to - City] field works fine because it has the space between "Ship" and "to" so MS says, "OK, looks like this is intentional, don't auto-format."  If you renmae the column in the table to [Shipto - City], then the auto-format hits the space hyphen space and decides to take action by reformatting.  So, other than clubbing HKEY_Current_User and messing with MS Office as a whole, is there a way to tell just this query to stop re-formatting?

Tom

See my above comment, which will stop the self-torture you are currently experiencing and allow you to control your object names.  -Jim

harfang,

I used the MS Access query design view.  Double clicked the fields, select 'Append' from the menu option 'Query'.  Looks fine initially, but after saving, closing, then reopening the query, the field is reformatted.

At this point, the easiest thing would be to just vba the append and use the 'RunCode' selection in Macros.

Still, I'd rather have the solution to the query problem.

Wow, with two different tables, this does happen!

However, it is only a bug in the query assistant. Namely when the query is analyzed and chopped up into tiny bits for the query assistant. If you save and modify your query in SQL view, there is no problem. Also, if you add the spaces and save, the query is saved correctly and works as expected.

You only need to correct this each time you open the query in the assistant...

Cheers!
(°v°)

Note that Jim's solution is best in the long  run. Just take half a day and apply a good naming convention to your tables, fields and queries. It is worth it, and normally the first task whenever I take over a database (^v^)

Another workaround: create a query that aliases all the fields, e.g.

    strTerritoryDesc: [Territory - Desc]

You can also give to these queries the names that the tables should have, e.g. tblYourTableName. Then you can start developping using those in place of the tables, until you find the time to change the names for good...

(°v°)

Jim,

You are right about the self torture piece.  Like most big system ERP users, we like to pull the data out of our million dollar oracle/JDE environment and run reports in access and excel.  Makes me laugh every day!!!  The field name defined in JDE has spaces.  When I perform a data pull, I get the column header.  When I import (through a macro running vba - again, more laughter) I wind up with a table that has the bad conventions.

Here's an option available in SQL, though I haven't figured it out in Access: How do I do an Alter Table - Column Name?  If Access allows, I can change the foolish name, append, then change back in three queries in a macro.  The column name with spaces works in all selects and joins, it only fails in the append.  I just want to be able to append without having to manually change the column name every time.

harfang,

I've tried the sql view - with all bracketing in place - it doesn't keep.  I haven't used the DAO before.  Do I just write a new module with the DAO line above?  Then call the module from a Macro?

T

harfang,

I will be redeploying the database in the future and will rename fields.  For now, the DAO above works best.  I have already implemented it in vba.  It works!!!

T

As long as this is a temporary workaround, go with it! I'm glad it worked.
(°v°)