Conditional Formatting for Date Field

[JamesJ]

I have the following expression to set the font color to green if:
[ReminderDate]<Date(). (less than today's date) One record is dated
yesterday
but it doesn't seem to be working. I have 2 other conditions that work ok.

Any help will be appreciated,
James

 

[Ken Snell MVP]

What format is ReminderDate field? Try this:

CDate([ReminderDate])<Date()

 

[JamesJ]

It's a Date/Time

What format is ReminderDate field? Try this:

CDate([ReminderDate])<Date()

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

I have the following expression to set the font color to green if:
[ReminderDate]<Date(). (less than today's date) One record is dated
yesterday
but it doesn't seem to be working. I have 2 other conditions that work
ok.

Any help will be appreciated,
James

 

[JamesJ]

Ken, I found that my other Conditional Format expression is taking
precedence over this expression. The records in this table are assigned
to a field in a look-up table - Appointment, Task and Recurring. I have a
conditional
format that if the ReminderType is Appointment (1) then I have the text in
red.
This expression is: [ReminderTypeID]=1. When I change the ReminderType to
something other that 1 for the record the other conditional format fires.
Not sure how to remedy the situation.

James

What format is ReminderDate field? Try this:

CDate([ReminderDate])<Date()

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

I have the following expression to set the font color to green if:
[ReminderDate]<Date(). (less than today's date) One record is dated
yesterday
but it doesn't seem to be working. I have 2 other conditions that work
ok.

Any help will be appreciated,
James

 

[JamesJ]

Excuse the way I'm replying here. Not sure if I should reply to myself
(seems odd) or
to Ken.
Anyway, I found what is causing this. About a month ago I posted a question
to display the date field as 'Today'
if the value of ReminderDate is Date() using the Select statement for the
form. It works but the Conditional
formatting seems to get messed up now. Also, when I change the sign to >
(greater than) even though the date is yesterday
it still triggers the condition for greater than today's date.
When I removed the part form the Select statement to display the date field
to 'Today' for the form and display the field
normally the conditional formatting works fine. So I guess it's one or the
other unless someone can come up with an alternative.
I hope someone understand this 'cause I don't. My explanations aren't very
intelligible.

James

What format is ReminderDate field? Try this:

CDate([ReminderDate])<Date()

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

I have the following expression to set the font color to green if:
[ReminderDate]<Date(). (less than today's date) One record is dated
yesterday
but it doesn't seem to be working. I have 2 other conditions that work
ok.

Any help will be appreciated,
James

 

[Ken Snell MVP]

Assuming that the RemainderDate is bound to a field, use the value of the
field, not the control, for your conditional formatting comparison. Assuming
that the field name is NOT the same as the control name, just use the field
name in place of RemainderDate in your conditional format test expression
("Expression Is").

If they're the same name, then you will need to change one of them so that
they're different, then use the field name in the test.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Excuse the way I'm replying here. Not sure if I should reply to myself
(seems odd) or
to Ken.
Anyway, I found what is causing this. About a month ago I posted a
question to display the date field as 'Today'
if the value of ReminderDate is Date() using the Select statement for the
form. It works but the Conditional
formatting seems to get messed up now. Also, when I change the sign to >
(greater than) even though the date is yesterday
it still triggers the condition for greater than today's date.
When I removed the part form the Select statement to display the date
field to 'Today' for the form and display the field
normally the conditional formatting works fine. So I guess it's one or the
other unless someone can come up with an alternative.
I hope someone understand this 'cause I don't. My explanations aren't very
intelligible.

James

What format is ReminderDate field? Try this:

CDate([ReminderDate])<Date()

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

I have the following expression to set the font color to green if:
[ReminderDate]<Date(). (less than today's date) One record is dated
yesterday
but it doesn't seem to be working. I have 2 other conditions that work
ok.

Any help will be appreciated,
James

 

[JamesJ]

You've lost me.
This is the sql for the form with the addition to display 'Today' if
ReminderDate is Date():

SELECT IIf([ReminderDate]=Date(),"Today",Format([ReminderDate],"ddd"",
""mm/dd")) AS Expr1, *
FROM tblReminders WHERE (((ReminderDate)<Date()+6)) ORDER BY ReminderDate,
Reminder;

The Conditional Formatting works fine when I remove the immediate IIf and
simply use ReminderDate the formatting is ok.

ReminderDate is bound to a field and is the name of the field.

I don't understand when you say use the value of the field not the control.
I tried replacing ReminderDate in the [ReminderDate]<Date() with
[Expr1]<Date()
and it didn't work. I'm sure that's not what you meant.

Please elaborate,
James

Assuming that the RemainderDate is bound to a field, use the value of the
field, not the control, for your conditional formatting comparison.
Assuming that the field name is NOT the same as the control name, just use
the field name in place of RemainderDate in your conditional format test
expression ("Expression Is").

If they're the same name, then you will need to change one of them so that
they're different, then use the field name in the test.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Excuse the way I'm replying here. Not sure if I should reply to myself
(seems odd) or
to Ken.
Anyway, I found what is causing this. About a month ago I posted a
question to display the date field as 'Today'
if the value of ReminderDate is Date() using the Select statement for the
form. It works but the Conditional
formatting seems to get messed up now. Also, when I change the sign to >
(greater than) even though the date is yesterday
it still triggers the condition for greater than today's date.
When I removed the part form the Select statement to display the date
field to 'Today' for the form and display the field
normally the conditional formatting works fine. So I guess it's one or
the other unless someone can come up with an alternative.
I hope someone understand this 'cause I don't. My explanations aren't
very intelligible.

James

What format is ReminderDate field? Try this:

CDate([ReminderDate])<Date()

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I have the following expression to set the font color to green if:
[ReminderDate]<Date(). (less than today's date) One record is dated
yesterday
but it doesn't seem to be working. I have 2 other conditions that work
ok.

Any help will be appreciated,
James

 

[JamesJ]

Ken, I understand you now!.
Expr1 Name is ReminderDate. I renamed it to ReminderDate1 and of
course left the Control Source and it works.
I have no idea why but it did.

Thanks,
James

Assuming that the RemainderDate is bound to a field, use the value of the
field, not the control, for your conditional formatting comparison.
Assuming that the field name is NOT the same as the control name, just use
the field name in place of RemainderDate in your conditional format test
expression ("Expression Is").

If they're the same name, then you will need to change one of them so that
they're different, then use the field name in the test.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Excuse the way I'm replying here. Not sure if I should reply to myself
(seems odd) or
to Ken.
Anyway, I found what is causing this. About a month ago I posted a
question to display the date field as 'Today'
if the value of ReminderDate is Date() using the Select statement for the
form. It works but the Conditional
formatting seems to get messed up now. Also, when I change the sign to >
(greater than) even though the date is yesterday
it still triggers the condition for greater than today's date.
When I removed the part form the Select statement to display the date
field to 'Today' for the form and display the field
normally the conditional formatting works fine. So I guess it's one or
the other unless someone can come up with an alternative.
I hope someone understand this 'cause I don't. My explanations aren't
very intelligible.

James

What format is ReminderDate field? Try this:

CDate([ReminderDate])<Date()

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I have the following expression to set the font color to green if:
[ReminderDate]<Date(). (less than today's date) One record is dated
yesterday
but it doesn't seem to be working. I have 2 other conditions that work
ok.

Any help will be appreciated,
James

 

[Ken Snell MVP]

Sorry that I wasn't able to get back to you until now.

When a control and a field are named the same, ACCESS can get confused when
you use just the name, without any object qualifiers, in an expression. When
in doubt, ACCESS usually chooses the control. Because you're writing a value
into the control, that is the value that ACCESS sees when your conditional
formatting logic test is performed. What you really wanted was the value of
the date in the field, not the "Today" string or "ddd" string that you
displayed in the control via the control source (Expr1, which is the field
from the query that gives the form the data).

When you renamed your control to RemainderDate1, and left the RemainderDate
in the conditional formatting expression, you removed the ambiguity for
ACCESS and it now understands that you want to test the value of the field,
and not the control.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Ken, I understand you now!.
Expr1 Name is ReminderDate. I renamed it to ReminderDate1 and of
course left the Control Source and it works.
I have no idea why but it did.

Thanks,
James

Assuming that the RemainderDate is bound to a field, use the value of the
field, not the control, for your conditional formatting comparison.
Assuming that the field name is NOT the same as the control name, just
use the field name in place of RemainderDate in your conditional format
test expression ("Expression Is").

If they're the same name, then you will need to change one of them so
that they're different, then use the field name in the test.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Excuse the way I'm replying here. Not sure if I should reply to myself
(seems odd) or
to Ken.
Anyway, I found what is causing this. About a month ago I posted a
question to display the date field as 'Today'
if the value of ReminderDate is Date() using the Select statement for
the form. It works but the Conditional
formatting seems to get messed up now. Also, when I change the sign to >
(greater than) even though the date is yesterday
it still triggers the condition for greater than today's date.
When I removed the part form the Select statement to display the date
field to 'Today' for the form and display the field
normally the conditional formatting works fine. So I guess it's one or
the other unless someone can come up with an alternative.
I hope someone understand this 'cause I don't. My explanations aren't
very intelligible.

James

What format is ReminderDate field? Try this:

CDate([ReminderDate])<Date()

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I have the following expression to set the font color to green if:
[ReminderDate]<Date(). (less than today's date) One record is dated
yesterday
but it doesn't seem to be working. I have 2 other conditions that
work ok.

Any help will be appreciated,
James

 

[JamesJ]

Thanks much,
James

Sorry that I wasn't able to get back to you until now.

When a control and a field are named the same, ACCESS can get confused
when you use just the name, without any object qualifiers, in an
expression. When in doubt, ACCESS usually chooses the control. Because
you're writing a value into the control, that is the value that ACCESS
sees when your conditional formatting logic test is performed. What you
really wanted was the value of the date in the field, not the "Today"
string or "ddd" string that you displayed in the control via the control
source (Expr1, which is the field from the query that gives the form the
data).

When you renamed your control to RemainderDate1, and left the
RemainderDate in the conditional formatting expression, you removed the
ambiguity for ACCESS and it now understands that you want to test the
value of the field, and not the control.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Ken, I understand you now!.
Expr1 Name is ReminderDate. I renamed it to ReminderDate1 and of
course left the Control Source and it works.
I have no idea why but it did.

Thanks,
James

Assuming that the RemainderDate is bound to a field, use the value of
the field, not the control, for your conditional formatting comparison.
Assuming that the field name is NOT the same as the control name, just
use the field name in place of RemainderDate in your conditional format
test expression ("Expression Is").

If they're the same name, then you will need to change one of them so
that they're different, then use the field name in the test.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Excuse the way I'm replying here. Not sure if I should reply to myself
(seems odd) or
to Ken.
Anyway, I found what is causing this. About a month ago I posted a
question to display the date field as 'Today'
if the value of ReminderDate is Date() using the Select statement for
the form. It works but the Conditional
formatting seems to get messed up now. Also, when I change the sign to
(greater than) even though the date is yesterday
it still triggers the condition for greater than today's date.
When I removed the part form the Select statement to display the date
field to 'Today' for the form and display the field
normally the conditional formatting works fine. So I guess it's one or
the other unless someone can come up with an alternative.
I hope someone understand this 'cause I don't. My explanations aren't
very intelligible.

James

What format is ReminderDate field? Try this:

CDate([ReminderDate])<Date()

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


I have the following expression to set the font color to green if:
[ReminderDate]<Date(). (less than today's date) One record is dated
yesterday
but it doesn't seem to be working. I have 2 other conditions that
work ok.

Any help will be appreciated,
James