Forum Discussion

LBaumgart's avatar
LBaumgart
Copper Contributor
Apr 21, 2025

Select Query values not showing in report - MS Access

I'm probably missing something simple, but..

I have a Table that has 4 field (plus key), Company ID, Join Date, Quit Date, Notes. This tracks agency membership 

I made a select query, with 2 IIF statements, and 2 conversions. My Datasheet view shows correctly, but when i put the fields on a report, YRSCALC is empty, and doesn't sum, which affects Grand Total. I have tried every type of formatting i can think of..

Select Query:

SELECT tblJOINQUIT.[Company ID#], tblJOINQUIT.[Join Date], tblJOINQUIT.[Quit Date], tblJOINQUIT.Notes,

Sum(IIf([Quit Date] Is Null,"0.00",DateDiff("m",[Join Date],[Quit Date]))) AS NumOfMonthsCalc,

Sum(IIf([Quit Date] Is Not Null,"0.00",DateDiff("m",[Join Date],Now()))) AS NumOfMonthsCurr,

[NumOfMonthsCalc]/12 AS YRSCALC,

[NumOfMonthsCurr]/12 AS YRSCURR

FROM tblJOINQUIT

GROUP BY tblJOINQUIT.[Company ID#], tblJOINQUIT.[Join Date], tblJOINQUIT.[Quit Date], tblJOINQUIT.Notes;

Report View:

Help??

access

3 Replies

Sort By
  • Kidd_Ip's avatar
    Kidd_Ip
    MVP
    Apr 22, 2025

    Any chance to use a subquery:

     

    SELECT CompanyID, JoinDate, QuitDate, Notes, 
       NumOfMonthsCalc, NumOfMonthsCurr, 
       NumOfMonthsCalc / 12 AS YRSCALC, 
       NumOfMonthsCurr / 12 AS YRSCURR
FROM 
   (SELECT tblJOINQUIT.[Company ID#] AS CompanyID, tblJOINQUIT.[Join Date] AS JoinDate, 
           tblJOINQUIT.[Quit Date] AS QuitDate, tblJOINQUIT.Notes AS Notes,
           Sum(IIf([Quit Date] Is Null, 0, DateDiff("m", [Join Date], [Quit Date]))) AS NumOfMonthsCalc,
           Sum(IIf([Quit Date] Is Not Null, 0, DateDiff("m", [Join Date], Now()))) AS NumOfMonthsCurr
    FROM tblJOINQUIT
    GROUP BY tblJOINQUIT.[Company ID#], tblJOINQUIT.[Join Date], tblJOINQUIT.[Quit Date], tblJOINQUIT.Notes) AS SubQuery;

     

Resources