Report Conditional Formating in Access 2007

[sphinney]

I'm trying to change the font color of a textbox on a report to red if the date displayed in the textbox is before today. I'm using the following code to accomplish this:

Dim Ctrl1 as Textbox
Dim CFO As FormatCondition
Dim EXP1str as String
 
EXP1str = "[" & Ctrl1.Name & "]<" & Date
 
Set CFO = Ctrl1.FormatConditions.Add(acExpression, , EXP1str )
CFO.ForeColor = RGB(255, 0, 0)

The problem is the value I'm assigning to "EXP1str" is not being evaluated by Access the way I am expecting. Can anyone tell me how to build the value for EXP1str? Or should the type of condition be set to "acFieldValue" instead of "acExpression"? There's not a lot of guidance in the help files about this.

Thanks in advance for your help!

[sphinney]

OK, I'm still experimenting trying to figure out this problem. I'm trying to turn the text red in a textbox (on a report) if the date contained in the textbox is before the current date. I've found that if I use the code below, Access evaluates the condition in a way I don't understand. Sometimes the dates before the current date are not turned red and sometimes the dates after the current date are turned red. Ug!

Dim Ctrl1 as Textbox
Dim CFO As FormatCondition
 
'The control has the same name as the control source its associated with
 
Set CFO = Ctrl1.FormatConditions.Add(acExpression, , "[" & Ctrl1.Name & "] < Date()")
CFO.ForeColor = RGB(255, 0, 0)

This should be so simple but I'm clearly missing something. Can anyone tell me what I'm doing wrong?

[missinglinq]

I'll be honest, I haven't got a clue as to what you're trying to do with this code, and I suspect by the lack of responses that no else does either. But this kind thing is easily done using Conditional Formatting, assuming you're running Access 2000 or later.

1. In Form Design View, click on the textbox to select it
2. Goto Format - Conditional Formatting and set up the boxes as:
3. "Field Value Is" "Less Than" Date()
4. Now, set the font/forecolor to Red
5. Click "OK"

Linq ;0)>

[sphinney]

I'll be honest, I haven't got a clue as to what you're trying to do with this code, and I suspect by the lack of responses that no else does either. But this kind thing is easily done using Conditional Formatting, assuming you're running Access 2000 or later.

1. In Form Design View, click on the textbox to select it
2. Goto Format - Conditional Formatting and set up the boxes as:
3. "Field Value Is" "Less Than" Date()
4. Now, set the font/forecolor to Red
5. Click "OK"

Linq ;0)>

Thanks for responding missinglinq. I'm sorry that my post was so confusing. I think, sometimes, longer posts get less attention because folks don't have the time to wade through a ton of irrelavent details. So I try to keep my posts as short as possible so I don't waste people's time. I guess my brevity came at the expense of clarity. My apollogies.

For the problem I'm having, I'm already using the conditional formating. However, instead of using the conditional formatting dialog box by right-clicking on the textbox while the report is in design view, I was setting up the conditional formatting using code in the report's OnOpen event. My report actually has a dozen or more textboxes that I want to set up the exact same conditional formatting. So it seemed to me to be more efficient to use VB code to do it.

In the code I provided earlier, Ctrl1 is the generic name of a textbox on the report. CFO is the name of a FormatCondition object. The following line of code creates a new condition for the textbox:

Set CFO = Ctrl1.FormatConditions.Add(acExpression, , "[" & Ctrl1.Name & "] < Date()")

I named the textbox (Ctrl1) the same as the control source field (which is a date/time field type). Thus, the third argument of the line of code above should be evaluated as "[Ctrl1] < Date()" where the value of Ctrl1 is some date.

The next line of code sets the text color to red if the condition above is true:

CFO.ForeColor = RGB(255, 0, 0)

To me, this fairly simple and straight forward. However, like I said earlier, Access is evaluating the condition in some unknow manner. I say this because when my report runs the text for some records where the value of Ctrl1 is before today is not turned red (even though it meets the condition Ctrl1 < Date() ) and the text for some records where the value of Ctrl1 is later than today is turned red.

I wanted some help to figure out how Access is evaluating the condition "Ctrl1 < Date()" so I can adjust the syntaxt to get the result I desire (any date before today is shown in red text).

I'm sorry if I've been too long winded. I'm just trying to avoid confusing folks, again.

Thanks again for your help.

[Stewart Ross]

Hi. Date comparisons in code can cause difficulties, because unless a control is bound to a date-time field the values need to be coerced to a date type for such comparisons to work consistently.

I tested this by adding a control to a form and performing a comparison of that unbound control to the current date. I used the following debug code in the After Update event of the text control to show the values :

MsgBox ("Direct test: " & (Me.Text5 < Date) & " CDate test: " & (CDate(Me.Text5) < Date))

The second, type-converted, test consistently gave the correct result, the direct test an incorrect result.

Date/time values are stored internally as numeric values with the integer part the date and the decimal part the time. The date part counts the number of days from a particular reference date (1 Jan 1900 for dates in Microsoft products).

When a date is typed into a control what is displayed by the chosen date format as a human-readable date is quite different to what is stored internally in a date/time field. Although Access does its own type conversions when it can, it can sometimes cause further confusion as Access will flip day and month values to see if it can get a valid date from what has been typed (recognising that the ANSI SQL standard is for dates to be in American m/d/y format which may not be the same as the regional settings for the PC).

This is a long explanation for what may be causing you difficulties. Try a CDate type conversion and see how you get on.

-Stewart

[Avellano]

Hi
I would forget conditional formatting in reports when things are complicated

Below lines of code used in the OnPrint event of Detail1

If Me!machStatus = "On Loan" And Me!Days >= 120 And Me!Days < 180 Then
Me!Days.ForeColor = 128
ElseIf Me!machStatus = "On Loan" And Me!Days >= 180 Then
Me!Days.ForeColor = 255
Me!Plus180.ForeColor = 255
Else
Me!Days.ForeColor = 0
Me!Plus180.ForeColor = 0
End If

If Me!RVDate < Date Then
Me!RVDate.ForeColor = 255
Else
Me!RVDate.ForeColor = 0
End If

If Me!Plus180 < Date Then
Me!Plus180.ForeColor = 255
Else
Me!Plus180.ForeColor = 0
End If

If Me!machStatus = "Off Lease" Then Me!RVDate.ForeColor = 255

If Me!machStatus = "On Lease" And Me!Year = DatePart("yyyy", Date) Then
Me.Detail1.BackColor = 10079487
Else
Me.Detail1.BackColor = 16777215
End If

Works a treat

Have fun

Avellano

[sphinney]

Hi. Date comparisons in code can cause difficulties, because unless a control is bound to a date-time field the values need to be coerced to a date type for such comparisons to work consistently.

I tested this by adding a control to a form and performing a comparison of that unbound control to the current date. I used the following debug code in the After Update event of the text control to show the values :

MsgBox ("Direct test: " & (Me.Text5 < Date) & " CDate test: " & (CDate(Me.Text5) < Date))

The second, type-converted, test consistently gave the correct result, the direct test an incorrect result.

Date/time values are stored internally as numeric values with the integer part the date and the decimal part the time. The date part counts the number of days from a particular reference date (1 Jan 1900 for dates in Microsoft products).

When a date is typed into a control what is displayed by the chosen date format as a human-readable date is quite different to what is stored internally in a date/time field. Although Access does its own type conversions when it can, it can sometimes cause further confusion as Access will flip day and month values to see if it can get a valid date from what has been typed (recognising that the ANSI SQL standard is for dates to be in American m/d/y format which may not be the same as the regional settings for the PC).

This is a long explanation for what may be causing you difficulties. Try a CDate type conversion and see how you get on.

-Stewart

BINGO! The CDate function did it. I modified my code to the following and it works perfectly:

Set CFO = Ctrl1.FormatConditions.Add(acExpression, , "CDate([" & Ctrl1.Name & "].Value) < Date()")
CFO.ForeColor = RGB(255, 0, 0)

Thanks Stewart! And thanks to Missinglinq and Avellano for your help, too.

Scott