Feb 19 2022 11:54 AM
Hello,
I am trying to conditional format [TimePeriod] if its in the current month.
I did as follows but it doesnt format the months yellow and instead formats the Year.
Grateful for the help.
Conditional Format:
Output:
I need it to format the current month.
SELECT "Monthly" AS [Date Period], Format([WithdrawalDate],'mmmm yyyy') AS [TimePeriod], format(Sum(tblDraws_CAWC.LocalAcctNoLR),"Standard") AS LocalAcctNoLR, format(Sum(tblDraws_CAWC.LiquidityRes),"Standard") AS LiquidityResOnly, format(Sum(tblDraws_CAWC.TLocalAcctCalc),"Standard") AS TLocalAcctCalc
FROM tblDraws_CAWC
GROUP BY Format([WithdrawalDate],'mmmm yyyy')
UNION
SELECT "Yearly" AS [Date Period], Format([WithdrawalDate],'yyyy') AS [TimePeriod], format(Sum(tblDraws_CAWC.LocalAcctNoLR),"Standard") AS LocalAcctNoLR, format(Sum(tblDraws_CAWC.LiquidityRes),"Standard") AS LiquidityResOnly, format(Sum(tblDraws_CAWC.TLocalAcctCalc),"Standard") AS TLocalAcctCalc
FROM tblDraws_CAWC
GROUP BY Format([WithdrawalDate],'yyyy');
Feb 19 2022 01:09 PM
Solution
First, we have to be clear on what Format does, and what it doesn't do.
Dates are dates as a datatype, but you can change the way they look, i.e. you can FORMAT the date to display in any one of a huge number for ways, or, formats. The thing is, Format returns a string that just looks to you like it would be a date displayed in a specific way. It's a string.
In order for your conditional formatting to work as you want, you would have to apply it to the unformatted dates, not the strings resulting from the formats. But that isn't really what I think you want to have happen anyway. What I "THINK" you want, is for the records which have "Monthly" in the "Date Period" field to be highlighted, while the other records, which have "Yearly" in the "Date Period" field should not be highlighted, or highlighted in a different color. Is that not correct?
If my interpretation IS accurate, change the formula so that it refers to the correct field, i.e. "DatePeriod" and make the criteria whether that field contains "Monthly" or "Yearly"
Feb 19 2022 03:14 PM
Feb 19 2022 03:21 PM
Feb 19 2022 01:09 PM
Solution
First, we have to be clear on what Format does, and what it doesn't do.
Dates are dates as a datatype, but you can change the way they look, i.e. you can FORMAT the date to display in any one of a huge number for ways, or, formats. The thing is, Format returns a string that just looks to you like it would be a date displayed in a specific way. It's a string.
In order for your conditional formatting to work as you want, you would have to apply it to the unformatted dates, not the strings resulting from the formats. But that isn't really what I think you want to have happen anyway. What I "THINK" you want, is for the records which have "Monthly" in the "Date Period" field to be highlighted, while the other records, which have "Yearly" in the "Date Period" field should not be highlighted, or highlighted in a different color. Is that not correct?
If my interpretation IS accurate, change the formula so that it refers to the correct field, i.e. "DatePeriod" and make the criteria whether that field contains "Monthly" or "Yearly"