Alias outfield column header

[PatK]

I have a question about Aliasing fields in a query, if that it the right
terminology.

I have linked my Access DB to an external sharepoint list. The person who
created it labeled the heading of one column: Prod (start month).

I am creating a query and while I am at it, I want to simply this name, to
Sdate. So, I have entered this in the field column for this field: Sdate:
Prod (start month)

Unfortunately, while it gives no error, the output column is still labelled
Prod (start month). When I look at the sql query, I see:

SELECT DISTINCTROW ... [DB Upgrades Funnel].[Prod (Month Start)] AS SDate, ...

SO this is as I would expect, but the output table heading is not Sdate, as
I would hope. Ideas? ARe those parentheses in the table header row of the
source list confounding the situation?

Many THanks!

Patk

 

[Bob Barrows]

I have a question about Aliasing fields in a query, if that it the
right terminology.

I have linked my Access DB to an external sharepoint list. The
person who created it labeled the heading of one column: Prod (start
month).

I am creating a query and while I am at it, I want to simply this
name, to Sdate. So, I have entered this in the field column for this
field: Sdate: Prod (start month)

Unfortunately, while it gives no error, the output column is still
labelled Prod (start month). When I look at the sql query, I see:

SELECT DISTINCTROW ... [DB Upgrades Funnel].[Prod (Month Start)] AS
SDate, ...

SO this is as I would expect, but the output table heading is not
Sdate, as
I would hope. Ideas? ARe those parentheses in the table header row
of the source list confounding the situation?

That is puzzling. i think we need to see the complete sql.

In the meantime: you should evaluate whether you really need that
DISTINCTROW instead of DISTINCT. It is usually used to make a query
updatable that would otherwise be non-updatable if DISTINCT were used.
Here is what online help has to say:

DISTINCTROW has an effect only when you select fields from some, but not
all, of the tables used in the query. DISTINCTROW is ignored if your query
includes only one table, or if you output fields from all tables.

 

[John Spencer]

You might try making the field a calculated field if that doesn't cause you
problems elsewhere.

DateAdd("D",0,[DB Upgrades Funnel].[Prod (Month Start)]) AS SDate

If you are looking at this in Data Sheet view Access has the nasty behavior
(in my opinion) of showing the CAPTION property as the column name. It will
even grab the caption from the field property of the table if one is assigned
there and not in the query.

I don't know that this work the same way with Sharepoint, but I would guess
that it may.

On the other hand if you are using this query as the source for a form or
report you will need to refer to the field by the alias. Since my
applications avoid users seeing queries directly the caption issue is
generally not a problem for me.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[PatK]

Had an issue for a couple days, and could not see replies (nor am I getting
emails that replies were posted) so sorry for delay. Bottom line, since I
thought this had not been posted, I reposted last eve, the part about the
aliasing. See:

https://www.microsoft.com/office/co...8ada&catlist=&dglist=&ptlist=&exp=&sloc=en-us

The solution suggested there, worked. Ie, going to design view of the query,
right clicking on the field and changing the Caption to what I want. It
worked. Unexplainable. Wonder if it is due to something with Sharepoint
(and my not having read-access to the source list? ) Voodoo. Appreciate
your response!

PatK

I have a question about Aliasing fields in a query, if that it the
right terminology.

I have linked my Access DB to an external sharepoint list. The
person who created it labeled the heading of one column: Prod (start
month).

I am creating a query and while I am at it, I want to simply this
name, to Sdate. So, I have entered this in the field column for this
field: Sdate: Prod (start month)

Unfortunately, while it gives no error, the output column is still
labelled Prod (start month). When I look at the sql query, I see:

SELECT DISTINCTROW ... [DB Upgrades Funnel].[Prod (Month Start)] AS
SDate, ...

SO this is as I would expect, but the output table heading is not
Sdate, as
I would hope. Ideas? ARe those parentheses in the table header row
of the source list confounding the situation?

That is puzzling. i think we need to see the complete sql.

In the meantime: you should evaluate whether you really need that
DISTINCTROW instead of DISTINCT. It is usually used to make a query
updatable that would otherwise be non-updatable if DISTINCT were used.
Here is what online help has to say:

DISTINCTROW has an effect only when you select fields from some, but not
all, of the tables used in the query. DISTINCTROW is ignored if your query
includes only one table, or if you output fields from all tables.


--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


.