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

I have several columns with the format text sp hyphen sp text.  examples are [Territory - Desc] and [Ship to - City].  When I create append queries, the destination field name becomes reformatted (blank spaces removed) after saving the query.  Oddly, this is only happening to the [Territory - Desc] field.  The [Ship to - City] field is fine.  Changing the column name is easy, rewriting the all of the queries and reports that feed off of the existing column name would be a huge pain.  I just want Access to stop reformatting the destination field name in the append query.

This question is on your site a couple of times, but no real solution is currently out there.
tntmoellerAsked:
Who is Participating?
 
harfangConnect With a Mentor Commented:
tntmoeller,

You do not need to rename the columns. You could "pull" the data and append it to a table with good column names, or use a "wrapper query" changing all the names for the rest of your application.

However, you can use three queries to rename a table: "ALTER TABLE table ADD COLUMN newname...", then "UPDATE table SET newname=oldname", and "ALTER TABLE table DROP COLUMN oldname...". This is not ideal, but I don't know of a syntax for renaming fields.

It's much easier using DAO:

    CurrentDb.TableDefs("table").Fields("oldname").Name = "newname"

Finally, about "having to manually change the column name every time", it is not every time you use it, only every time you reopen it in design view. And if you save it in SQL view, not even then.

(°v°)
0
 
jefftwilleyCommented:
Can you paste the SQL for your query here?
J
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
tntmoellerAuthor Commented:
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?
0
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
I'm not even going to go there.  Instead...

Rick Fisher's 'Find & Replace utility' (http://www.rickworld.com, free 30-day trial, $39 buy) is an Access add-in that allows you to find and replace strings in your Access database, and can do this in all areas:  Tables, table definitions, queries, query definitions, forms, form controls, reports, report controls, database properties, and all VBA code.   This way, you can change table names and change all references to it at the same time.  I've been using this for eight years now and love it.

Speed Ferret (http://www.moshannon.com, free limited 30-day trial, $199 buy) does exactly the same thing, but with Visual Basic, SQL Server, and Access all at the same time.

Hope this helps.
-Jim
0
 
harfangCommented:
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°)
0
 
tntmoellerAuthor Commented:
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
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
See my above comment, which will stop the self-torture you are currently experiencing and allow you to control your object names.  -Jim
0
 
tntmoellerAuthor Commented:
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.
0
 
harfangCommented:
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°)
0
 
harfangCommented:
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°)
0
 
tntmoellerAuthor Commented:
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.
0
 
tntmoellerAuthor Commented:
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
0
 
tntmoellerAuthor Commented:
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
0
 
harfangCommented:
As long as this is a temporary workaround, go with it! I'm glad it worked.
(°v°)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.