Conditional Format (in a Union)

[Tony2021]

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');

[#2 · Accepted solution]

@Tony2021 

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"

[#3]

thank you, George. I added a column keeping the original date format in tact and used the FIRST to keep the sum (I will hide the new column as its not impt). I should have noticed that "mmm yyyy" format is not a real date but as you said it only looks like a date. I then applied the conditional format to each field by adding a "look back" to the date with the "real" date format. It works.

thank you very much!

[#4]

Using Access Date/Time values and Format() for display are among the trickier aspects of working in Access.

Continued success with the project.