Renaming query fields

[John T Ingato]

I must be missing something

I have a query created in design view which pulls the following fields from
two aliases of one table:
a.ReportDate
b.ReportDate

I have entered into each query design field:
Field: FromDate: ReportDate
Table: a

Field ToDate
Table: b
Criteria: DateAdd("d",7,[a].[ReportDate])

When I run the query each field shows up as ReportDate, not From or To

I highlighted the first field and changed the caption to FromDate and that
worked... Oddly though, the SQL never changed.

The beginning part of the SQL shows:
SELECT a.ReportDate AS FromDate, b.ReportDate AS ToDate,

a.ReportDate shows up as FromDate, b.ReportDate shows up as ReportDate

Can anyone tell me why?

 

[David F Cox]

Is the caption property set in the table design?

 

[John T Ingato]

I am not fond of the property page either. I prefer to hard code as much as
possible. Here is the SQL.

SELECT a.ReportDate AS FromDate, b.ReportDate AS ToDate, a.StoreNumber,
a.SKUNumber, tblProductLine.ItemNumber, tblProductLine.SkuDescription,
tblProductLine.[Buy Pack], a.OnHand, b.OnHand, NZ(a.onhand-b.onhand) AS
SalesThisWeek,
CInt(IIf(NZ(a.OnHand<b.OnHand),CalculatedSales(b.OnHand,a.OnHand,[Buy
Pack]),NZ(a.OnHand-b.OnHand))) AS AdjustedSalesThisWeek,
IIf([salesThisWeek]<0,([AdjustedSalesThisWeek]-[SalesThisWeek]),Null) AS
Received
FROM tblProductLine INNER JOIN ((tbl_HD_2006_852_POS_Data AS a INNER JOIN
tbl_HD_2006_852_POS_Data AS b ON (a.StoreNumber = b.StoreNumber) AND
(a.SKUNumber = b.SKUNumber)) INNER JOIN tblProgramHomeDepot ON b.SKUNumber =
tblProgramHomeDepot.SKU) ON tblProductLine.ItemNumber =
tblProgramHomeDepot.ItemNumber
WHERE (((b.ReportDate)=DateAdd("d",7,[a].[ReportDate])))
ORDER BY a.ReportDate, a.StoreNumber;

The Caption property doesn't change the SQL. As a personal aside, I hate
the
query caption property as it can make troubleshooting someone else's work
a
real PITA.

Please show us the entire SQL. Open the query in design view. Next go to
View, SQL View and copy and past it here. Information on primary keys and
relationships would be a nice touch too.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I must be missing something

I have a query created in design view which pulls the following fields
from
two aliases of one table:
a.ReportDate
b.ReportDate

I have entered into each query design field:
Field: FromDate: ReportDate
Table: a

Field ToDate
Table: b
Criteria: DateAdd("d",7,[a].[ReportDate])

When I run the query each field shows up as ReportDate, not From or To

I highlighted the first field and changed the caption to FromDate and
that
worked... Oddly though, the SQL never changed.

The beginning part of the SQL shows:
SELECT a.ReportDate AS FromDate, b.ReportDate AS ToDate,

a.ReportDate shows up as FromDate, b.ReportDate shows up as ReportDate

Can anyone tell me why?

 

[John Spencer]

It sounds as if the caption property got set on the query fields. Access
has the surprising (annoying to me) habit of using the caption property of
the field in the table to supply the column name when you display the query
results.

So check the table field tbl_HD_2006_852_POS_Data.ReportDate to see if the
caption property is set.

I am not fond of the property page either. I prefer to hard code as much
as possible. Here is the SQL.

SELECT a.ReportDate AS FromDate, b.ReportDate AS ToDate, a.StoreNumber,
a.SKUNumber, tblProductLine.ItemNumber, tblProductLine.SkuDescription,
tblProductLine.[Buy Pack], a.OnHand, b.OnHand, NZ(a.onhand-b.onhand) AS
SalesThisWeek,
CInt(IIf(NZ(a.OnHand<b.OnHand),CalculatedSales(b.OnHand,a.OnHand,[Buy
Pack]),NZ(a.OnHand-b.OnHand))) AS AdjustedSalesThisWeek,
IIf([salesThisWeek]<0,([AdjustedSalesThisWeek]-[SalesThisWeek]),Null) AS
Received
FROM tblProductLine INNER JOIN ((tbl_HD_2006_852_POS_Data AS a INNER JOIN
tbl_HD_2006_852_POS_Data AS b ON (a.StoreNumber = b.StoreNumber) AND
(a.SKUNumber = b.SKUNumber)) INNER JOIN tblProgramHomeDepot ON b.SKUNumber
= tblProgramHomeDepot.SKU) ON tblProductLine.ItemNumber =
tblProgramHomeDepot.ItemNumber
WHERE (((b.ReportDate)=DateAdd("d",7,[a].[ReportDate])))
ORDER BY a.ReportDate, a.StoreNumber;

The Caption property doesn't change the SQL. As a personal aside, I hate
the
query caption property as it can make troubleshooting someone else's work
a
real PITA.

Please show us the entire SQL. Open the query in design view. Next go to
View, SQL View and copy and past it here. Information on primary keys and
relationships would be a nice touch too.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I must be missing something

I have a query created in design view which pulls the following fields
from
two aliases of one table:
a.ReportDate
b.ReportDate

I have entered into each query design field:
Field: FromDate: ReportDate
Table: a

Field ToDate
Table: b
Criteria: DateAdd("d",7,[a].[ReportDate])

When I run the query each field shows up as ReportDate, not From or To

I highlighted the first field and changed the caption to FromDate and
that
worked... Oddly though, the SQL never changed.

The beginning part of the SQL shows:
SELECT a.ReportDate AS FromDate, b.ReportDate AS ToDate,

a.ReportDate shows up as FromDate, b.ReportDate shows up as ReportDate

Can anyone tell me why?

 

[John T Ingato]

That was it. I designed that table quite some time ago and I did set the
cation of the table. I never would have thought the caption transfered
over.

Thanks

It sounds as if the caption property got set on the query fields. Access
has the surprising (annoying to me) habit of using the caption property of
the field in the table to supply the column name when you display the
query results.

So check the table field tbl_HD_2006_852_POS_Data.ReportDate to see if
the caption property is set.

I am not fond of the property page either. I prefer to hard code as much
as possible. Here is the SQL.

SELECT a.ReportDate AS FromDate, b.ReportDate AS ToDate, a.StoreNumber,
a.SKUNumber, tblProductLine.ItemNumber, tblProductLine.SkuDescription,
tblProductLine.[Buy Pack], a.OnHand, b.OnHand, NZ(a.onhand-b.onhand) AS
SalesThisWeek,
CInt(IIf(NZ(a.OnHand<b.OnHand),CalculatedSales(b.OnHand,a.OnHand,[Buy
Pack]),NZ(a.OnHand-b.OnHand))) AS AdjustedSalesThisWeek,
IIf([salesThisWeek]<0,([AdjustedSalesThisWeek]-[SalesThisWeek]),Null) AS
Received
FROM tblProductLine INNER JOIN ((tbl_HD_2006_852_POS_Data AS a INNER JOIN
tbl_HD_2006_852_POS_Data AS b ON (a.StoreNumber = b.StoreNumber) AND
(a.SKUNumber = b.SKUNumber)) INNER JOIN tblProgramHomeDepot ON
b.SKUNumber = tblProgramHomeDepot.SKU) ON tblProductLine.ItemNumber =
tblProgramHomeDepot.ItemNumber
WHERE (((b.ReportDate)=DateAdd("d",7,[a].[ReportDate])))
ORDER BY a.ReportDate, a.StoreNumber;

The Caption property doesn't change the SQL. As a personal aside, I hate
the
query caption property as it can make troubleshooting someone else's
work a
real PITA.

Please show us the entire SQL. Open the query in design view. Next go
to
View, SQL View and copy and past it here. Information on primary keys
and
relationships would be a nice touch too.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I must be missing something

I have a query created in design view which pulls the following fields
from
two aliases of one table:
a.ReportDate
b.ReportDate

I have entered into each query design field:
Field: FromDate: ReportDate
Table: a

Field ToDate
Table: b
Criteria: DateAdd("d",7,[a].[ReportDate])

When I run the query each field shows up as ReportDate, not From or To

I highlighted the first field and changed the caption to FromDate and
that
worked... Oddly though, the SQL never changed.

The beginning part of the SQL shows:
SELECT a.ReportDate AS FromDate, b.ReportDate AS ToDate,

a.ReportDate shows up as FromDate, b.ReportDate shows up as ReportDate

Can anyone tell me why?