Spaces Stripped from Append Query Field

[alawless]

MS Access 2003 is stripping out two spaces from a field name that one of my append queries uses to append data from one table to another table.

In both tables the field names is "RR - Quoted".

I have selected this field name from the query Design View drop down. However, when I save the append query then reopen it the field name has changed to be "RR-Quoted" (ie the spaces between the first word and the dash and the dash and the second word have been stripped out).

Can you (a) explain why this happens, and (b) how to stop Access doing this.

Regards Andrew

[RuralGuy]

Try specifying the name as [RR - Quoted].

[alawless]

Hi Rural Guy,
Already done - used [RR - Quoted].
Still strips the spaces out - ie the query won't retain the changes made.
Andrew

[June7]

The records do not append? I could not replicate the issue in Access 2010.

Why do you need to append records - sounds like duplication of data?

Recommend not using spaces, punctuation (underscore is exception), special characters in names. Causes issues, as you have discovered.

[alawless]

Thanks for your reply.

For some reason the designer of this database set things up to import a weekly csv data set into an initial table to then append from that table to a master table of all FY records.

I agree that special characters should be avoided, however in this case to change it would require modification of many dependent queries and dependent pivot tables which I was trying (lazyly) to avoid.

I might see if we can import straight to the master table instead.

[orange]

Can you post a copy of the mdb? Remove any confidential or private data.

[June7]

Might invest in Rick Fisher's Find & Replace, about $50. It has been a life saver for me.

[RuralGuy]

I would try *importing* your db into a fresh new db and see if the problem follows: http://www.btabdevelopment.com/ts/impnew

[alawless]

I tried copying in the tables and queries to a blank database but the same issue persisted.

I have attached a stripped down version of the database that demonstrates the issue. It is called Andrew.mdb.

Thanks for your interest and support.

[orange]

On your database, the append query that I received is

INSERT INTO EstimageAllQuotes ( [RR-Quoted] )
SELECT RawEstimage.[RR - Quoted]
FROM RawEstimage;

If I open your query in design mode, and check the output field by clicking the drop down , I see and select [RR - Quoted] as below

INSERT INTO EstimageAllQuotes ( [RR - Quoted] )
SELECT RawEstimage.[RR - Quoted]
FROM RawEstimage;

And I thought either you have some corruption, or somehow happened to drop the space in front of Quoted?????

But, when I saved the append query, this is what got saved

INSERT INTO EstimageAllQuotes ( [RR-Quoted] )
SELECT RawEstimage.[RR - Quoted]
FROM RawEstimage;

I have no idea as to why it is happening --haven't noticed this behavior before.

However, I did notice you have a macro defined in your database 4RunEstimage.

I don't use macros, but did notice a reference to Table EstimageAllQuotes_Backup. I don't know if this is responsible for the behavior.
I'm running Acc2003 on XP.

[alawless]

Yes it's a little mystery indeed! Could this be a bug in 2003?
The macro simply makes a backup copy of the table before new data is appended to it, and runs the import of the excel data.

[June7]

I opened the file in Access2010, put the spaces back in the field name, saved, viewed again, spaces still there. Closed file, reopened, checked the query, spaces still there.

The macro won't run because of missing file so can't say if that is somehow corrupting the query. I don't use macros either. I would code this append in VBA.