Conditional Format using MONTH

[steve711]

I have searched for this topic but haven't seen one just yet.

My problem:

I have a form that has a date and the date backcolor must be one of 4 colors. The conditions.

The date should be yellow if the current month is the month prior to the fields month.

The date should be green if the current month is the same as the fields month.

The date should be red if the current month is one month after the fields month.

I have come up formatting that works, however, I am using the date()-30 etc. function but this is not what I need. It must be based on the month and not days.

I am fumbling with DateAdd("m",date(),1) but I haven't got a clue as to the arguments for this.

 

[Milothicus]

I think you can use something involving month(txtdate) to use the month part of a txtdate control.

 

[steve711]

I now have a slightly better understanding of dateadd. However it doesn't quite do what I need.

For example. Today is Nov 2. If the date field I have is Nov 1 the color should not be RED. However it is showing up as RED. Oct 31st is RED as it should be.

Here is what I used for the RED statement

[AS_293b_Exp]<DateAdd("m",0,Date()) = RED

How can I make it so that it is purely based on the MONTH not the length of time of a month as it is doing now.

 

[Milothicus]

Month function

Returns a Variant (Integer) specifying a whole number between 1 and 12, inclusive, representing the month of the year.

Syntax

Month(date)

look into it.

 

[steve711]

I have and I am using it to some degree. However then the problem becomes the year so I have enclosed it within year as well.

These solutions are working just not as precisely as I want, not yet....

 

[Milothicus]

how about comparing years, and adding 12 to one if the years are different?

more detail....

if you're looking at december 04 and jan 05, the month function will give you 12 and 1. if you can determine that jan's year is one greater than dec's year, you can add 12 to jan's, and get 12 and 13 as your months, which i think will help your analysis......