Conditional Formatting

[Jacob Frankham]

Hi all

I have a textbox on a report ehich is a date

I have set the conditional formatting so that whenever Field Value Is: Less
than or equal to: Now() it will show the date in red (ie those dates in the
PAST)

However, SOME dates in 2004 are also appearing in red (some 2004 dates are
also NOT appearing in red)

Any ideas, completely baffled me !!


Jake

 

[Jacob Frankham]

It might be worth adding gere, that it is a textbox which has a
controlsource property assigned to one of the queries fields,
(as opposed to a calculated textbox ie =IIf( etc))

Cheers

Jake

 

[Marshall Barton]

It might be worth adding gere, that it is a textbox which has a
controlsource property assigned to one of the queries fields,
(as opposed to a calculated textbox ie =IIf( etc))

Cheers

Jake

Just guessing here, but by any chance is the field in the
table a Text field (even if it looks like a date? Or maybe
the field in the query is calculated in such a way that it
results in a text string?

 

[Jacob Frankham]

Hi Marshall (Again !)

No, the field in the query is v much a date

Totally confused now

Jake!

 

[Marshall Barton]

No, the field in the query is v much a date

Is there a pattern to the dates that show up in red and
normal color? If you can't see one, post a half dozen
examples of each so maybe someone can spot a problem.

As an experiment, try changing the text box's control source
from fieldname to =CDate(fieldname)
--
Marsh
MVP [MS Access]

 

[Jacob Frankham]

Marshall

Just read your last post, but have solved the problem anyway.

If you are interested, the answer was this:

I had a date field in my query which contained some Null values
I had an Iif statement in the date field such that =Iif([dueDate] Is Null,
"ASAP",[dueDate])

I guess then, that this turned the date field into a String field (as you
correctly stated earlier) and thus was confusing the Conditional Formatting
of the textbox.

So, I removed the Iif statement, and applied it to the textbox control on
the report and this sorted it

Cheers

Jake

 

[Marshall Barton]

Marshall

Just read your last post, but have solved the problem anyway.

If you are interested, the answer was this:

I had a date field in my query which contained some Null values
I had an Iif statement in the date field such that =Iif([dueDate] Is Null,
"ASAP",[dueDate])

I guess then, that this turned the date field into a String field (as you
correctly stated earlier) and thus was confusing the Conditional Formatting
of the textbox.

So, I removed the Iif statement, and applied it to the textbox control on
the report and this sorted it

Thanks for letting us know what the problem turned to be.
You have to watch out for Access' auto type casting
unexpectedly changing things into something else.

I'm glad you've got it sorted (and vindicated my original
guess too ;-)
--
Marsh
MVP [MS Access]