Conditional Formatting

[Shane Hill]

In a text box on a form I need the background to turn red if the date in the
text box is not within the current half of the year or current year... so if
the date in the text box is 22 Feb 08, and today's date is 15 Aug 08, it
needs to be red. Or if the date in the text box is 12 Oct 07, and today's
date is 20 Sep 08, it needs to be red. Any suggestions?

 

[Michael]

Hi Shane,

If the textbox is populated with data when the form opens, simply add an
event handler to the form's On Open event.

If a user enters a value into the textbox then add an event handler to the
After Update event.

In both cases you could use the following:

Dim lngDate as long, lngTestDate as long
' the 1 July of the current year - ie half year start
lngDate=Dateserial(Year(date),month(7),day(1))
' the text box's value/date
lngTestDate = Dateserial(year(txtBox1),month(txtBox1),day(txtBox1))

' set the default background color to white
me!txtBox1.backcolor=16777215

' evaluate the text box date
if lngTestDate < lngDate then me!txtBox1.backcolor=255

I hope the above is of any help...
Cheers
Michael

 

[Jeanette Cunningham]

Shane,
assuming A2000 and above
--select the text box
--Format | Conditional Formatting
--use the dialog that opens to set up the formats you want

Here are some examples of date functions you can use
Also check out vba help on DateDiff

If Date1 > Date2 Then ...

If Datediff("d", Date1, Date2) < 0 Then ...

If Date2 - Date1 < 0 Then ...

Date1 + 7 adds 7 days to the date

DateAdd("d", 7, Date1)

Jeanette Cunningham

 

[Linq Adams via AccessMonster.com]

"If the textbox is populated with data when the form opens, simply add an
event handler to the form's On Open event"

Formatting code should NEVER go in the On Open event! This will do the
formatting according to the value in the first record, and all records
thereafter will be formatted the same way, regardless of their value! This
type of code belongs in the Form_Current event.

Assuming A2000 or greater, Jeanette's advice is spot on!

 

[Michael]

Hi Shane,

I agree with Jeanette and Linq Adams, their advice is correct and most of
all very simple.

I agree also with Linq Adams re never to place the code into the On Open
event. However, I believe this advice is only applicable if the form's record
source is based on a table, which I ususally never do.

Sorry for having caused some confusion.

Cheers
Michael

 

[Linq Adams via AccessMonster.com]

Doesn't matter what the record source is, table or query, formatting code
never goes in OnOpen, or you'll experience the problem I stated above.