Solved

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

Posted on 2006-06-19
15
470 Views
Last Modified: 2013-02-09
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.
0
Comment
Question by:tntmoeller
  • 6
  • 5
  • 3
  • +1
15 Comments
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
Can you paste the SQL for your query here?
J
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
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
 

Author Comment

by:tntmoeller
Comment Utility
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
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 100 total points
Comment Utility
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
 
LVL 58

Expert Comment

by:harfang
Comment Utility
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
 

Author Comment

by:tntmoeller
Comment Utility
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
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
See my above comment, which will stop the self-torture you are currently experiencing and allow you to control your object names.  -Jim
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:tntmoeller
Comment Utility
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
 
LVL 58

Expert Comment

by:harfang
Comment Utility
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
 
LVL 58

Expert Comment

by:harfang
Comment Utility
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
 

Author Comment

by:tntmoeller
Comment Utility
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
 
LVL 58

Accepted Solution

by:
harfang earned 400 total points
Comment Utility
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
 

Author Comment

by:tntmoeller
Comment Utility
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
 

Author Comment

by:tntmoeller
Comment Utility
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
 
LVL 58

Expert Comment

by:harfang
Comment Utility
As long as this is a temporary workaround, go with it! I'm glad it worked.
(°v°)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now