Conditional Format form - between date and 30 days

[Tony2021]

Hello Experts,

I have a form in datasheet view.

I need to conditional format txtDate for any records in the current month.  Its a little tricky since if there is not a record with the current month (April in this case) then check the next month.  So in the below, 5/22/5022 should be shaded yellow but if there wasnt any record in May then I would want to shade if there is a record in the next month June.    Basically, just shade the nearest record and if there are 2 records in the same month then shade both (this is why I use Month(Date()) in the below.  

I am using Expression Is: 

=Month([txtDate]) Between Month(Date()) And DateAdd("d",30,Date())

the part that is after "And" might not be the correct approach since there could be a case that there is no record within 30 days (see bolded text above).  

below is the output (its not correct, should only shade 5/22/2022: 

thank you in advance.

[#2]

@Tony2021 

First, to be clear, the screen shot shows TWO sections with yellow highlights, one for the coming month of May, 2022 and the other for dates between May, 2023 and December, 2023. That doesn't square up with what your description seems to specify.

Please clarify that. Why is the second section highlighted?

In any event, this is not going to be an easy to implement requirement, I don't think, because conditional formatting only applies to the current instance of a control on a form, whereas you seem to need to compare both the current value of any given control, and the values of all other controls to be sure none of them match the "current month" criteria.

Actually, it might help if you could also elaborate on why this needs to be done. Sometimes context is king, well, actually, context is nearly always critical to understanding a request for a solution.

[#3]

HI George,
thanks for the response. The screen grab is not correct since the formula is not correct.
What I need to do is highlight any transactions in the current month but if there are none then highlight the next closest.
Let me know if not clear. thank you

[#4 · Accepted solution]

you can simplify your Criteria:

[txtDate] Between DateSerial(Year(Date()), Month(Date()), 1) And DateAdd("d",30,Date())

Note i remove the equal (=) symbol.

[#5]

@arnel_gp 

Hi Arnel, it works!  thank you so much.  

Below is the output (it correctly highlights the next record in the next month since there are no records in April). thanks again for the help.