Forum Discussion
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.
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;- 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.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.
