append query adds square brackets

[franklin97355]

When I create an append query the append to fields have square brackets if there is a space in the name but the query won't work until I remove the brackets. Is there a way to tell access not to put the brackets in? Access 2007 on Vista business. The spaces are there so the table will format with the correct column headings when exported to excel.

The answer is "42"

 

[genomon]

Don't use 2007 or Vista, but can you just change the caption field property in QBE to say what you need?

Money can't buy happiness -- but somehow it's more comfortable to cry in a Corvette than in a Yugo.

 

[franklin97355]

I'm still looking for a solution for this. Here is the sql query that the query generates with the brackets removed.

INSERT INTO [Map CRF Export] ( [School Name], [School Number], [Previous Teacher ID], [Teacher ID], [Teacher Last Name], [Teacher First Name], [Teacher Middle Initial], [Class Name], [Class Period Name], [Class Subject Name], [Previous Student ID], [Student ID], [Student Last Name], [Student First Name], [Student Middle Initial], [Student Date of Birth], [Student Gender], [Student Grade], [Student Ethnic Group Name], [Student Language Preference Name], ESL, IEP, Title, 504 )
SELECT DISTINCT [Map Complete].[School Name], [Map Complete].[School Number], [Map Complete].[Previous Teacher ID], [Map Complete].[Teacher ID], [Map Complete].[Teacher Last Name], [Map Complete].[Teacher First Name], [Map Complete].[Teacher Middle Initial], [Map Complete].[Class Name], [Map Complete].[Class Period Name], [Map Complete].[Class Subject Name], [Map Complete].[Previous Student ID], [Map Complete].[Student ID], [Map Complete].[Student Last Name], [Map Complete].[Student First Name], [Map Complete].[Student Middle Initial], [Map Complete].[Student Date of Birth], [Map Complete].[Student Gender], [Map Complete].[Student Grade], [Map Complete].[Student Ethnic Group Name], [Map Complete].[Student Language Preference Name], [Map Complete].ESL, [Map Complete].IEP, [Map Complete].Title, [Map Complete].[504]
FROM [Map Complete];

The answer is "42"

 

[lespaul]

If you have spaces in your field names Access requires you to wrap them in brackets...

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[franklin97355]

lespaul, I hear what you are saying. My question is why then does the append query refuse to run until I remove the brackets in the query design window and then runs without complaint? It looks like the part that creates the query does not conform to the part that executes the query.

The answer is "42"

 

[lespaul]

I guess I misunderstood what the issue was....

The question is "Ultimate Question of Life, the Universe, and Everything"....from another Hitchhiker fan!

 

[fneily]

You posted the SQL that worked. Can you post the SQL that doesn't work?
Also, are you typing in any []'s?

 

[franklin97355]

fneily, I guess I can't. While trying to do what you asked I discovered that if I open the SQL view and tpye anything even a space that I then remove the query works and if I go back to design view the brackets are gone. Go figure.

The answer is "42"

 

[JoeAtWork]

Sigh, I don't use Access2007 yet. But Microsoft seems to have a habit of "making things easier" which cause nothing but problems. In this case I'm guessing they think the square brackets confuse us poor demented users, so they hide them in the design view. In the raw SQL they certainly are still needed.