Conditional Formatting with a Date Function

  • Thread starter Thread starter rm
  • Start date Start date
R

rm

Two fields on a report [timing] and [started] If started > timing + 10
minutes then the text should be red.

In the Conditional Formatting form the following has been entered for
Condition 1.

Expression 1 is [started]>DateAdd("n",10,[timing])

This does not work.

Also tried Field Value is Greater than [timing] + 10

Also tried Expression 1 is DateDiff("n", [timing], [started]) > 10.

No luck.

The help file is of no value. For example, nowhere in the text is
there any indication that "[" and "]" must surround a field name.
 
Two fields on a report [timing] and [started] If started > timing + 10
minutes then the text should be red.

In the Conditional Formatting form the following has been entered for
Condition 1.

Expression 1 is [started]>DateAdd("n",10,[timing])

This does not work.

Also tried Field Value is Greater than [timing] + 10

Also tried Expression 1 is DateDiff("n", [timing], [started]) > 10.

No luck.

The help file is of no value. For example, nowhere in the text is
there any indication that "[" and "]" must surround a field name.
Click to expand...

A Date datatype field value in Access must be a complete date and time
value.
I would guess that [timing] is NOT actually a date/time value (i.e.
1/5/2010 3:15:00 PM) but rather a *length* of time value (i.e. 3
hours 15 minutes) even though you may have set the field's datatype to
Date/Time which is not a date. Therefore 3 hours and 15 minutes + 10
minutes = 3 hours and 25 minutes, which is never going to be >
1/5/2010 3:15:00 PM.

Assuming my guess is correct, here is a comparison of actual values
from the debug window after the 10 minutes has been added to the 3:15
PM [timing] time:

[Started]
?cdbl(#1/5/2010 03:15:00 PM#)
40183.6354166667
[Timing] if stored in a Date/Time field:
?cdbl(#3:25 PM#)
0.642361111111111

You'll notice the [Started] value, which included the date value of
(40183.) is much greater than the [timing] value.

Copy and paste into a reply your exact conditional formatting
expression, as well as an example of the data actually stored in
[Timing] and in [Started] and their datatypes and we'll see what we
can do for you.

Note: It's always a good idea to surround field names with brackets.
 
Back
Top