Forum Discussion

Tony2021's avatar
Tony2021
Steel Contributor
Sep 18, 2024

Current Date in Select Query

Experts, 

 

I am trying to make this query and I think I am pretty close but I dont know how to show the Current Date in the blue below? 

 

SELECT T1.Expirey2,T1.AmountOfChange, T1.LCID
(SELECT SUM(AmountOfChange)
FROM qryLCAmends AS T2

WHERE T2.LCID = T1.LCID
AND T2.Expirey2 < the current date)
AS LCBalance
FROM qryLCAmends AS T1
ORDER BY LCID;

 

the format of Expirey2 is like 9/17/2024

I think I need a NZ handler as well.

thank you

 

  • SELECT T1.Expirey2,T1.AmountOfChange, T1.LCID,
        (SELECT SUM(AmountOfChange)
         FROM qryLCAmends AS T2
         WHERE T2.LCID = T1.LCID
         AND T2.Expirey2 < DATE()) AS LCBalance
    FROM qryLCAmends AS T1
    ORDER BY LCID;

     

    This will return the detail rows returned by the outer query, and the sum of the AmountOfChange values for the current LCID value in each row?  Is that what's required, rather than returning the balance per transaction per subset of distinct LCID value?  If the latter, you might like to take a look at Balances.zip in my public databases folder at:

     

    https://1drv.ms/f/c/44cc60d7fea42912/EhIppP7XYMwggESpAAAAAAABaDKZCllSuweYBPJ5zKa3cg

     

    This little demo file illustrates a number of queries for returning balances in different contexts, either over all rows or per group.

  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor

    Tony2021 You need DATE() function. DATE() function will return current date.

    select date() as [CurrentDate];

     In case of your SQL, it will be-

    SELECT T1.Expirey2,T1.AmountOfChange, T1.LCID
    (SELECT SUM(AmountOfChange)
    FROM qryLCAmends AS T2
    
    WHERE T2.LCID = T1.LCID
    AND T2.Expirey2 < date())
    AS LCBalance
    FROM qryLCAmends AS T1
    ORDER BY LCID;
    • Tony2021's avatar
      Tony2021
      Steel Contributor
      Hi Harun, thank you for the response.

      Sorry but I am not a programmer.
      I am not exactly sure how to combine both of the select queries?
      I dont think its like this, correct?
      select date() as [CurrentDate];
      SELECT T1.Expirey2,T1.AmountOfChange, T1.LCID
      (SELECT SUM(AmountOfChange)
      FROM qryLCAmends AS T2
      WHERE T2.LCID = T1.LCID
      AND T2.Expirey2 < date())
      AS LCBalance
      FROM qryLCAmends AS T1
      ORDER BY LCID;

      thank you for your help.
      • Ken_Sheridan's avatar
        Ken_Sheridan
        Brass Contributor

        SELECT T1.Expirey2,T1.AmountOfChange, T1.LCID,
            (SELECT SUM(AmountOfChange)
             FROM qryLCAmends AS T2
             WHERE T2.LCID = T1.LCID
             AND T2.Expirey2 < DATE()) AS LCBalance
        FROM qryLCAmends AS T1
        ORDER BY LCID;

         

        This will return the detail rows returned by the outer query, and the sum of the AmountOfChange values for the current LCID value in each row?  Is that what's required, rather than returning the balance per transaction per subset of distinct LCID value?  If the latter, you might like to take a look at Balances.zip in my public databases folder at:

         

        https://1drv.ms/f/c/44cc60d7fea42912/EhIppP7XYMwggESpAAAAAAABaDKZCllSuweYBPJ5zKa3cg

         

        This little demo file illustrates a number of queries for returning balances in different contexts, either over all rows or per group.

Resources