CODEWITHSUNDEEP
ms-accessreportalias
Kevin Watts
Kevin Watts

Reputation: 1

How do I use an Alias in my Acess report

I've created an alias in my Access SQL query, it shows fine as a valid column when I run my query, but when I try and use that field in my report, it reverts back to the original name and value. How do I make the report pull in the value of the alias returned in my record stack? Will I have to do this through VBA?

This is the query which is my report's Record Source:

SELECT
    ISO_ID.ISOID,
    ISO_Stats.TransactionCount,
    ISO_Stats.TerminalCount,
    ISO_Stats.MonthEnd,
    m2.TerminalCount,
    m2.TransactionCount,
    m2.MonthEnd,
    m3.TerminalCount,
    m3.TransactionCount,
    m3.MonthEnd
FROM
    (((ISO_ID
    INNER JOIN ISO_Stats AS m2
    ON ISO_ID.[ISOID] = m2.[ISOID])
    INNER JOIN ISO_Stats
    ON (ISO_ID.ISOID = ISO_Stats.ISOID) AND (ISO_ID.InstNbr = ISO_Stats.InstNbr))
    INNER JOIN ISO_Stats AS m3
    ON ISO_ID.[ISOID] = m3.[ISOID])
WHERE
        (((ISO_Stats.MonthEnd)=DateSerial(Year(Date()),Month(Date()),0))
    AND ((m2.MonthEnd)=DateSerial(Year(Date()),Month(Date())-1,0))
    AND ((m3.MonthEnd)=DateSerial(Year(Date()),Month(Date())-2,0))
    AND ((ISO_ID.Cancelled)<>"Y") )
ORDER BY ISO_ID.ISOName;

Upvotes: 0

Views: 201

Answers (1)

HansUp
HansUp

Reputation: 97101

Your query's SELECT column list includes duplicate column names. Use aliases so that the column names in the query result set are distinct:

SELECT
    ISO_ID.ISOID,
    ISO_Stats.TransactionCount AS [TransactionCount_i],
    ISO_Stats.TerminalCount AS [TerminalCount_i],
    ISO_Stats.MonthEnd AS [MonthEnd_i],
    m2.TerminalCount AS [TerminalCount_2],
    m2.TransactionCount AS [TransactionCount_2],
    m2.MonthEnd AS [MonthEnd_2],
    m3.TerminalCount AS [TerminalCount_3],
    m3.TransactionCount AS [TransactionCount_3],
    m3.MonthEnd AS [MonthEnd_3]

You will need to revise the report to use those aliased column names, but then you should be able to avoid the report's confusion when dealing with duplicate column names.

Upvotes: 1

Related Questions