Conditional Formatting

[Petros]

HI all,
I want to display and condition (colour) a date reference in a text field
Example: 11/07/2010, i use this as conditional formatting but want to replace Date() with month
Field value is: Between Date() and Date()+90 / Green
..Date() should be current month and +90 should 3 months
Field value is: greater than Date()=91 / Red
...Date() should be current month and +91 should remain as day count..
Is this possible?

Thanks!

 

[pbaldy]

It sounds like you want to start with the first day of the current month. You'll find the appropriate function here:

http://www.pacificdb.com.au/Support/code_date_monthfunctions.htm

 

[the_angry_monkey]

You can accomplish this quite easily using the conditional formatting button. I have this exact function running but using the command: value is between now()-90 and now()-180. Can be slow to update if you have a lot of records

 

[Petros]

You can accomplish this quite easily using the conditional formatting button. I have this exact function running but using the command: value is between now()-90 and now()-180. Can be slow to update if you have a lot of records

Hi, my current conditions are from that button in my application..but i need to find out a way to code the "on current" event of that specific control to format the condtions..especially to display current date in Month..its tricky since there are three conditions invloved.. Current date (month)..future date (withing next two months) and historic date ( < Date())...

 

[vbaInet]

Conditional Formatting normally allows for three conditions. But here's one from DCrake:

http://www.xcraftlimited.co.uk/Sample-Databases.html

At the bottom of the page you will find the folder CondFormat.zip

 

[Petros]

Hi, my current conditions are from that button in my application..but i need to find out a way to code the "on current" event of that specific control to format the condtions..especially to display current date in Month..its tricky since there are three conditions invloved.. Current date (month)..future date (withing next two months) and historic date ( < Date())...

I am using this in the forms current event..

If Me![Text1] = Date Then
Me.Text1.BackColor = vbRed
Me.Text1.ForeColor = vbWhite

The first issue i have to is replace Date with current Month..meaning that if Date is July then etc etc...i am getting gray hair on this one..

 

[vbaInet]

If Month(Me.Text1.value) = Month(Date) Then

 

[Petros]

If Month(Me.Text1.value) = Month(Date) Then

Yes...this is what i was looking for...i understand now..i need to refer to one the text1 control...

Thanks!

 

[vbaInet]

Glad we could help.

NB: You can also refer to the field as well. Try both and see how they work.

 

[Petros]

Glad we could help.

NB: You can also refer to the field as well. Try both and see how they work.

..just one humble question...

I use this code:

If Month(Text1.Value) < Month(Date) Then
Me.Text1.BackColor = vbGreen
Me.Text1.ForeColor = vbBlack
End If
If Month(Text1.Value) = Month(Date) Then
Me.Text1.BackColor = vbRed
Me.Text1.ForeColor = vbWhite
End If

But if i also want to say Month(Date) and the next 2 months..?
If Month(Text1.Value) = Month(Date) + 2 Then..does not work..

a datepart someware?

Sorry for distrurbing with such trivialites...

 

[DCrake]

If Month(Text1) = Month(DateAdd("m",2,Date)) Then

By using the DateAdd function we are adding2 months to the date THEN comparing the months AFTER.

with the above you are simply comparing month numbers, you may find it better using DateDiff instead

If DateDiff("d",Me.Text1,DateAdd("d",60,Date())) > 60 then

 

[Petros]

If Month(Text1) = Month(DateAdd("m",2,Date)) Then

By using the DateAdd function we are adding2 months to the date THEN comparing the months AFTER.

with the above you are simply comparing month numbers, you may find it better using DateDiff instead

If DateDiff("d",Me.Text1,DateAdd("d",60,Date())) > 60 then

Ok , thanks.. i will concentrate on this option too...despite the upcomming WorldCup Finale ..Thanks again!

 

[Brianwarnock]

Whilst this approach is correct as it allows for crossing year boundaries you should work in months not days to be correct.

Brian