Access 2007 query returning field names rather than alias

  • Thread starter Thread starter Dale Fye
  • Start date Start date
D

Dale Fye

I posted a similar message about a month ago, but got no suggestions that
helped.

I've got several queries which I have designed for export to Excel. As
such, I want the field names in the output to look like:

This instead of This
LD# LD_Num
Learning Obj Learning_Obj
Description LD_Desc

but these queries continue to return the "instead of this" field name rather
than the alias I have enclosed in brackets in the SQL statement.

I never use the fields Caption property in the base tables, so that has
nothing to do with it. However, I have noticed that if I fill in the Caption
property of the column in the query, then it shows up properly.

Anybody have any ideas why field alias is not working, and how to correct
this?
 
Dale

I saw no SQL-statement, so I'm hard pressed to offer reasons why that might
be happening.

By the way, when I create a dummy query in design view, create an 'output
field name', then check it in the SQL view, it looks like:

SELECT Sheet1.[Contract Type] AS X
FROM Sheet1;

I don't see square brackets around the rename ("X").

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Jeff,

I never put spaces in my actual field names, but do when I write a query
that is designed for export. In this particular case the query looks
something like (I'm at home, not work):

SELECT LD_Num as [LD #], Learning_Objective as [Learning Objective],
LD_Desc as Description, LD_Narrative as Narrative
FROM myTable
WHERE LD_Year = "FY10"

Actually it is quite a bit more complex as there are about 8 other tables
linked (via LEFT Joins) to this table via one or more ID foreign keys.
But NONE of the alias values show up as column headers when I run this
query, even the ones that are a single word.

Dale


Jeff Boyce said:
Dale

I saw no SQL-statement, so I'm hard pressed to offer reasons why that
might be happening.

By the way, when I create a dummy query in design view, create an 'output
field name', then check it in the SQL view, it looks like:

SELECT Sheet1.[Contract Type] AS X
FROM Sheet1;

I don't see square brackets around the rename ("X").

Regards

Jeff Boyce
Microsoft Office/Access MVP


Dale Fye said:
I posted a similar message about a month ago, but got no suggestions that
helped.

I've got several queries which I have designed for export to Excel. As
such, I want the field names in the output to look like:

This instead of This
LD# LD_Num
Learning Obj Learning_Obj
Description LD_Desc

but these queries continue to return the "instead of this" field name
rather
than the alias I have enclosed in brackets in the SQL statement.

I never use the fields Caption property in the base tables, so that has
nothing to do with it. However, I have noticed that if I fill in the
Caption
property of the column in the query, then it shows up properly.

Anybody have any ideas why field alias is not working, and how to correct
this?

--
Dale

email address is invalid
Please reply to newsgroup only.
Click to expand...
Click to expand...
 
Dale

I'm baffled. I haven't seen this behavior before.

Have you had any luck searching on-line?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Dale Fye said:
Jeff,

I never put spaces in my actual field names, but do when I write a query
that is designed for export. In this particular case the query looks
something like (I'm at home, not work):

SELECT LD_Num as [LD #], Learning_Objective as [Learning Objective],
LD_Desc as Description, LD_Narrative as Narrative
FROM myTable
WHERE LD_Year = "FY10"

Actually it is quite a bit more complex as there are about 8 other tables
linked (via LEFT Joins) to this table via one or more ID foreign keys.
But NONE of the alias values show up as column headers when I run this
query, even the ones that are a single word.

Dale


Jeff Boyce said:
Dale

I saw no SQL-statement, so I'm hard pressed to offer reasons why that
might be happening.

By the way, when I create a dummy query in design view, create an 'output
field name', then check it in the SQL view, it looks like:

SELECT Sheet1.[Contract Type] AS X
FROM Sheet1;

I don't see square brackets around the rename ("X").

Regards

Jeff Boyce
Microsoft Office/Access MVP


Dale Fye said:
I posted a similar message about a month ago, but got no suggestions that
helped.

I've got several queries which I have designed for export to Excel. As
such, I want the field names in the output to look like:

This instead of This
LD# LD_Num
Learning Obj Learning_Obj
Description LD_Desc

but these queries continue to return the "instead of this" field name
rather
than the alias I have enclosed in brackets in the SQL statement.

I never use the fields Caption property in the base tables, so that has
nothing to do with it. However, I have noticed that if I fill in the
Caption
property of the column in the query, then it shows up properly.

Anybody have any ideas why field alias is not working, and how to
correct
this?

--
Dale

email address is invalid
Please reply to newsgroup only.
Click to expand...
Click to expand...
Click to expand...
 
No.

Just trying to see whether anyone else had seen this.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



Jeff Boyce said:
Dale

I'm baffled. I haven't seen this behavior before.

Have you had any luck searching on-line?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Dale Fye said:
Jeff,

I never put spaces in my actual field names, but do when I write a query
that is designed for export. In this particular case the query looks
something like (I'm at home, not work):

SELECT LD_Num as [LD #], Learning_Objective as [Learning Objective],
LD_Desc as Description, LD_Narrative as Narrative
FROM myTable
WHERE LD_Year = "FY10"

Actually it is quite a bit more complex as there are about 8 other tables
linked (via LEFT Joins) to this table via one or more ID foreign keys.
But NONE of the alias values show up as column headers when I run this
query, even the ones that are a single word.

Dale


Jeff Boyce said:
Dale

I saw no SQL-statement, so I'm hard pressed to offer reasons why that
might be happening.

By the way, when I create a dummy query in design view, create an 'output
field name', then check it in the SQL view, it looks like:

SELECT Sheet1.[Contract Type] AS X
FROM Sheet1;

I don't see square brackets around the rename ("X").

Regards

Jeff Boyce
Microsoft Office/Access MVP


I posted a similar message about a month ago, but got no suggestions that
helped.

I've got several queries which I have designed for export to Excel. As
such, I want the field names in the output to look like:

This instead of This
LD# LD_Num
Learning Obj Learning_Obj
Description LD_Desc

but these queries continue to return the "instead of this" field name
rather
than the alias I have enclosed in brackets in the SQL statement.

I never use the fields Caption property in the base tables, so that has
nothing to do with it. However, I have noticed that if I fill in the
Caption
property of the column in the query, then it shows up properly.

Anybody have any ideas why field alias is not working, and how to
correct
this?

--
Dale

email address is invalid
Please reply to newsgroup only.
Click to expand...
Click to expand...
Click to expand...
 
I've seen a number of similar posts all over the net but no answer has been
given. As well, there seems to be a misunderstanding when it comes to
articulating the problem, which is quite easy to demonstrate.

I have a COMPANY table with a CO_NAME field that has a generic caption
labeled "Name." I then have several queries that query the COMPANY table and
each of them has a separate context. So in one context the the CO_NAME
refers to a leasing company whereas in another query it refers a renting
company. It is therefore highly desirable to use field aliases to reinforce
the context of the query. By design, the following should work:

SELECT CO_NAME AS [Lessor] ...

or

SELECT CO_NAME AS [Lessee] ...

Unfortunately, both queries return the generic caption "Name" defined in the
table structure as evidenced by the column headers of a listbox, combobox or
in the query designer itself.

The above referenced problem can be demonstrated with ease and is
responsible for many unanswered posts in many forums all over the net. How
MS continues to miss the issue is completely beyond me. It is clearly a bug,
and one that is hard not to notice if you use MS Access on anything other
than the most basic of levels.

To all those who absolutely need to correct this behavior there is a
workaround, but it comes at the price of sacrificing some performance. You
can assert your field aliases by turning the entire field specification into
a function. For some reason, this technique overrides the brain dead portion
of MS Access that ignores normal field aliases.

First, create a module level function such as

Function SetAlias(FieldValue) as string
SetAlias = FieldValue
End Function

Then instead of the following:

SELECT CO_NAME AS [Lessor] ...

Use:

SELECT SetAlias(CO_NAME) as [Lessor]...

This kind of treatment can get messy, especially when you're dealing with
many field aliases. But it works for situations where it's absolutely
necessary.

Get with the program Microsoft!
 
I realize this thread is 3 years old, but I have been scouring the internet trying to find the same solution and finally figured it out.

Solution:
Open the TABLE that your query is based on in design view within Access.
Select the field that your query refuses to use the alias for.
You will probably notice that the "Caption" value at the bottom of your screen is set to match the field name. DELETE the caption.
Save and close the table.
Save, close, re-open the query.

Your aliases should now be working!

You'll have to do that for every field of course that you find your aliases not working on.
 
Back
Top