Conditional Formatting With Months

[cameron.scrimgeour]

Hi

I have a subform in the datasheet view. I would like to have the data in it alternate different background colours depending on the month...

Jan-1990 = blue
Feb - 1990 = green
march - 1990 = blue
April - 1990 = green

and so on.

I think this should be quite simple to do.

If I can find out what month the next row of data is from then compare it to the current row and decide what colour to use.

But, how can I check the data held in the next row?

Seriously stumped on this one.

CHeers

 

[DCrake]

If you use the Mod function to find out if the row is an odd number or an even one you can apply the logic there.

David

 

[cameron.scrimgeour]

If you use the Mod function to find out if the row is an odd number or an even one you can apply the logic there.

David

ALready thought of that but if the data was as follow...

Jan - 1990
March - 1990

Both would be blue, in this scenario I would still want them to be Blue, then Green.

 

[DCrake]

Ok then convert your Jan - 2000 back top a meaningfull date and test that

iif(Month(CDate("01 - " & [Your period])) Mod 2=1,"Odd","Even")

 

[cameron.scrimgeour]

Ok then convert your Jan - 2000 back top a meaningfull date and test that

iif(Month(CDate("01 - " & [Your period])) Mod 2=1,"Odd","Even")

what?

No idea what your meaning.

Is there not just some way I can check the date in the next row and go...

if current row date = next row date then blue
else
green

 

[DCrake]

Irrespective of what the condition is both the options (mine and yours) are both doing the same thing in principle.

If .. Then .. Else

The way I suggested does not need to evaluate the next or previous row against the current row. Ask your self these questions

What happens if there is only one record in the table
What happens if I am at the first record in the table
What happens if I am at the last record in the table
You would have to cater for these eventualities, whereas my solution is only concerned with the contents of the current row.

All it is saying is "Is the month an odd number or an even number?" and based on the outcome it will colour the background accordingly.

David

 

[cameron.scrimgeour]

Irrespective of what the condition is both the options (mine and yours) are both doing the same thing in principle.

If .. Then .. Else

The way I suggested does not need to evaluate the next or previous row against the current row. Ask your self these questions

What happens if there is only one record in the table
What happens if I am at the first record in the table
What happens if I am at the last record in the table
You would have to cater for these eventualities, whereas my solution is only concerned with the contents of the current row.

All it is saying is "Is the month an odd number or an even number?" and based on the outcome it will colour the background accordingly.

David

but if 2 even months or 2 odd months were next to each other, they would be the same colour. I want them to be different. To alternate each time for a different month and year.

 

[DCrake]

I think there are examples on the forum that do what you want. Personally I have my own controls that have the alternate row functionality built into the control.

See image attachment.