none
Column Headings of query have changed RRS feed

  • Question

  • Is anyone aware of anything that would suddenly cause the column headings of a query to change?

    I have not made any changes to the database or query.

    'Allegation 1' is appearing as 'Allegation Code 1'. The caption in the database is set as 'Allegation 1'. The heading that is appearing may be from an old version of the table design or query.

    Here is the query:

    SELECT BA.CaseNumber AS [Case Number], CaseStatus.Description AS [Case Status], tblCaseTypes.CaseType AS [Case Type], BA.Title19 AS [Non-Title XIX], BA.ContactMethod AS [Contact Method], tblSubjectType.Desc AS [Subject Type], QS.ID AS [Subject ID], QS.ProviderType, BA.EligibilityAgency AS [Eligibility Agency], QS.Lastname AS [Last Name], QS.Firstname AS [First Name], BA.Target, BA.RelatedId AS [AHCCCS ID], ConvDate(QS.DOB) AS [Date of Birth], QS.PID AS SSN, QS.Citizen AS [US Citizen], QS.HomeStreet1, QS.HomeStreet2, QS.HomeCity, QS.HomeState, QS.HomeZip, QS.MailStreet1, QS.MailStreet2, QS.MailCity, QS.MailState, QS.MailZip, QS.Phone, BA.TaskForceCase AS [Taskforce?], BA.EDIWatch AS [LexisNexis Queried], BA.PITEval AS [PIT Eval], BA.ReferralSource AS Source, ConvDate(BA.ReferralReceived) AS [Referral Received Date], BA.Investigator AS Investigator, IT.Team, ConvDate(BA.AssignedDate) AS [Assigned Date], BA.ReferredTo AS [Referred To], ConvDate(BA.ReferredDate) AS [Referred To Date], BA.ResultofInvestigation AS Result, ConvDate(BA.InvestigationEndDate) AS [Investigation End Date], BA.AtRiskAmount, BA.Allegation1 AS [Allegation 1], Allegations_1.AllegationDescription AS [Allegation Description 1], BA.Allegation2 AS [Allegation 2], Allegations_2.AllegationDescription AS [Allegation Description 2], BA.Allegation3 AS [Allegation 3], Allegations_3.AllegationDescription AS [Allegation Description 3], BA.SourceCaseNumber AS [Source Case #], ConvDate(BA.CMPFiledDate) AS [CMP Filed], ConvDate(BA.CMPRcvdDate) AS [CMP Received], ConvDate(BA.CMPPlus30) AS [30 Day Date], ConvDate(BA.CMPPlus60) AS [60 Day Date], BA.Recoveries, BA.HealthPlanSavings AS [HP Recoveries/Sanctions], BA.ProgramSavings AS [Program Savings], BA.InvestigativeCosts AS [Investigative Costs Recovered], BA.ProsecutionCaseNumber AS [Prosecution Case #], BA.ProsecutionStatus AS [Prosecution Status], BA.PaymentsSuspended AS [Payments Suspended?], ConvDate(BA.SentenceDate) AS [Sentence Date], BA.SentenceTimeYears AS [Confinement Years], BA.SentenceTimeMonths AS [Confinement Months], BA.ProbationTimeYears AS [Probation Years], BA.ProbationTimeMonths AS [Probation Months], BA.CommunityServiceTimeHours AS [Community Service Hours], BA.AssetForfeiture AS [Asset Forfeiture], tblAR.DueAmt AS [Recoveries/Fine], qryAR.TotPaid AS [Amount Paid To Date], qryAR.BalDue AS [Balance Due], ConvDate(qryAR.LastPay) AS [Last Payment Date], tblAR.PayorName AS [Payor Name], tblAR.PayorStreet1 AS [Payor Street 1], tblAR.PayorStreet2 AS [Payor Street 2], tblAR.PayorCity AS [Payor City], tblAR.PayorState AS [Payor State], tblAR.PayorZip AS [Payor Zip], tblAR.PayAmt AS [Settlement Terms Payment Amount], ConvDate(tblAR.InitialPayDate) AS [Initlal Payment Date], tblPaymentInterval.Interval AS [Payment Interval], tblAR.NumPayments AS [Number Of Payments], BA.Comments, BA.Comments2, tblAR.CollectDate, tblAR.Notes, BA.LastUpdBy AS [CaseData_Last Updated By], ConvDT(BA.LastUpdDt) AS [CaseData_Last Updated], QS.LastUpdBy AS [Subject_Last Updated By], ConvDT(QS.LastUpdDt) AS [Subject_Last Updated], tblAR.LastUpdBy AS [AR_Last Updated By], ConvDT(tblAR.LastUpdDt) AS [AR_Last Updated]
    FROM (CaseStatus RIGHT JOIN (tblSubjectType RIGHT JOIN (qryInvestigatorTeams AS IT RIGHT JOIN (Allegations AS Allegations_2 RIGHT JOIN (Allegations AS Allegations_3 RIGHT JOIN (Allegations AS Allegations_1 RIGHT JOIN (tblPaymentInterval RIGHT JOIN (tblCaseTypes INNER JOIN (qrySubjects AS QS RIGHT JOIN (tblAR RIGHT JOIN [Beth Afirms] AS BA ON tblAR.CaseNumber = BA.CaseNumber) ON QS.ID = BA.SubjectId) ON tblCaseTypes.ID = BA.CaseTypeId) ON tblPaymentInterval.ID = tblAR.PayInterval) ON Allegations_1.AllegationCode = BA.Allegation1) ON Allegations_3.AllegationCode = BA.Allegation3) ON Allegations_2.AllegationCode = BA.Allegation2) ON IT.Investigator = BA.Investigator) ON tblSubjectType.ID = QS.Type) ON CaseStatus.CaseStatusCode = BA.CaseStatus) LEFT JOIN qryAR ON BA.CaseNumber = qryAR.CaseNumber
    WHERE BA.CaseNumber>=[From Case #] And BA.CaseNumber<=[To Case #];

    Tuesday, October 2, 2018 12:51 AM

Answers

  • I discovered something interesting.

    The front-end captions and the back-end captions are different.

    I go into the front end and the caption says 'Allegation Code 1'

    In the back-end it says 'Allegation 1'

    This is the same table and column, and the front-end is linked to the back-end.

    So what determines what I see when I run a query from the front-end?

    Will it use the caption from the front-end or back-end?

    My results indicate it was using the captions from the back-end but now has reverted to using the ones in the front-end.

    Wednesday, October 3, 2018 12:26 AM

All replies

  • Open the query in design view.

    Click in the Allegation 1 column.

    Activate the Property Sheet (press F4 if it is not displayed).

    Clear the Caption property.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, October 2, 2018 8:16 AM
  • Is anyone aware of anything that would suddenly cause the column headings of a query to change?

    Hi ATGNWT,

    Oops, what a query! Never experienced any change on my side..

    Imb.

    Tuesday, October 2, 2018 12:41 PM
  • When you specify AS [Newname] in a query, isn't that supposed to override the table caption specification? That's the whole reason I coded that, and it was working in this database until very recently. Something in the last month caused this glitch. We did all upgrade from Win 7 to Win 10 machines and got a new version of MS Access, still 2010 though.

    Tuesday, October 2, 2018 6:04 PM
  • Hans Vogelaar is correct.

    The Caption property in the Property Sheet overrides the AS Caption.


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Tuesday, October 2, 2018 8:02 PM
  • The Caption property is an Access interface property. It operates at a higher level than the SQL alias (FieldName AS Alias).

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, October 2, 2018 8:09 PM
  • So what you are saying is if you have defined a caption, there is no way to override it in a query?

    Is this something new?

    This query has worked perfectly in the past and no changes have been made to the query or table definition as far as I'm aware.

    The only thing I have done is a compact/repair on the back-end.





    Tuesday, October 2, 2018 9:36 PM
  • As far as I know, the Caption property overrides the Alias defined in SQL. I have no explanation for the change you experienced.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, October 2, 2018 9:45 PM
  • I discovered something interesting.

    The front-end captions and the back-end captions are different.

    I go into the front end and the caption says 'Allegation Code 1'

    In the back-end it says 'Allegation 1'

    This is the same table and column, and the front-end is linked to the back-end.

    So what determines what I see when I run a query from the front-end?

    Will it use the caption from the front-end or back-end?

    My results indicate it was using the captions from the back-end but now has reverted to using the ones in the front-end.

    Wednesday, October 3, 2018 12:26 AM