another conditional format question

[Guest]

I was reading on this post about using conditional formatting.
http://msdn.microsoft.com/newsgroup...-DC48AB0DA5C9&dglist=&ptlist=&exp=&sloc=en-us

“select "Expression Is" from the
combo box, and enter a valid boolean expression. Use the entire object
specifier rather than the shortcut Me!.“

I am trying to check the value of a date field in a subform.
Here is the expression I entered after selecting the field in the subform.

Forms![frm_Milestone_Release]![sfrm_Milestones_for_Release].Form.[txt_Status]<Date()

I have also tried to select "Field is" and choosing less than and entering
the expression Date(). Unfortunately, same result.

It seems to key off of the MM/DD and not year. For example if the date is
12/31/06, there is no formatting. If the date is 1/1/07, it is correctly
formatted red.

Can someone help correct my errors?

 

[Graham Mandeno]

Hi David

It seems to be interpreting the value in txt_Status as a text string, and
then doing a string comparison.

Try using the CDate function to explicitly convert the left-hand side to a
date.

Is the control you are conditionally formatting [txt_Status]? if so then it
should work just to say:
Field Value Is -- less than -- Date()

If it is another control on your subform, use:
Expression Is -- CDate([txt_Status]) < Date()

If it is a control on your main form, then try:
Expression Is -- CDate([sfrm_Milestones_for_Release].Form.[txt_Status])
< Date()

You should not need the entire object reference right down from the Forms
collection.

 

[Guest]

Graham,
Thank you for the response.
Since the problem is not easy, let me go into a bit more detail on what I
have done. Unfortunately, same problem.

I am on the subform, select the text box object titled txt_Status, then
choose Conditional formatting...
In the conditional format, I am using Expression Is
CDate([txt_Status])<Date()

The record source is a query. I have changed the query to this to try and
force it to be in the date format.
txt_Status: Format(IIf([Completed]=False,[Due_Date]," "),"mm/dd/yyyy")

Thank you for your patience, any other suggestions is appreciated. I will
also look at multiple criteria in the expression is instead of using the IIF
in the query.

Hi David

It seems to be interpreting the value in txt_Status as a text string, and
then doing a string comparison.

Try using the CDate function to explicitly convert the left-hand side to a
date.

Is the control you are conditionally formatting [txt_Status]? if so then it
should work just to say:
Field Value Is -- less than -- Date()

If it is another control on your subform, use:
Expression Is -- CDate([txt_Status]) < Date()

If it is a control on your main form, then try:
Expression Is -- CDate([sfrm_Milestones_for_Release].Form.[txt_Status])
< Date()

You should not need the entire object reference right down from the Forms
collection.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I was reading on this post about using conditional formatting.
http://msdn.microsoft.com/newsgroup...-DC48AB0DA5C9&dglist=&ptlist=&exp=&sloc=en-us

"select "Expression Is" from the
combo box, and enter a valid boolean expression. Use the entire object
specifier rather than the shortcut Me!."

I am trying to check the value of a date field in a subform.
Here is the expression I entered after selecting the field in the subform.

Forms![frm_Milestone_Release]![sfrm_Milestones_for_Release].Form.[txt_Status]<Date()

I have also tried to select "Field is" and choosing less than and entering
the expression Date(). Unfortunately, same result.

It seems to key off of the MM/DD and not year. For example if the date is
12/31/06, there is no formatting. If the date is 1/1/07, it is correctly
formatted red.

Can someone help correct my errors?

 

[Guest]

Graham,
Thanks for your help. Issue resolved. I just set multiple criteria using
other fields on the form in the conditional format expression and it worked.
In the words of missinglinq... There's ALWAYS more than one way to skin a
cat!

Hi David

It seems to be interpreting the value in txt_Status as a text string, and
then doing a string comparison.

Try using the CDate function to explicitly convert the left-hand side to a
date.

Is the control you are conditionally formatting [txt_Status]? if so then it
should work just to say:
Field Value Is -- less than -- Date()

If it is another control on your subform, use:
Expression Is -- CDate([txt_Status]) < Date()

If it is a control on your main form, then try:
Expression Is -- CDate([sfrm_Milestones_for_Release].Form.[txt_Status])
< Date()

You should not need the entire object reference right down from the Forms
collection.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I was reading on this post about using conditional formatting.
http://msdn.microsoft.com/newsgroup...-DC48AB0DA5C9&dglist=&ptlist=&exp=&sloc=en-us

"select "Expression Is" from the
combo box, and enter a valid boolean expression. Use the entire object
specifier rather than the shortcut Me!."

I am trying to check the value of a date field in a subform.
Here is the expression I entered after selecting the field in the subform.

Forms![frm_Milestone_Release]![sfrm_Milestones_for_Release].Form.[txt_Status]<Date()

I have also tried to select "Field is" and choosing less than and entering
the expression Date(). Unfortunately, same result.

It seems to key off of the MM/DD and not year. For example if the date is
12/31/06, there is no formatting. If the date is 1/1/07, it is correctly
formatted red.

Can someone help correct my errors?

 

[Graham Mandeno]

Hi David

Glad you got it working

For the record, given the extra info, I would not use [txt_Status] at all in
the conditional expression, but rather use the source fields from which
[txt_Status] is calculated:

Expression Is -- [Completed]=0 And [Due_Date]<Date()

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,
Thank you for the response.
Since the problem is not easy, let me go into a bit more detail on what I
have done. Unfortunately, same problem.

I am on the subform, select the text box object titled txt_Status, then
choose Conditional formatting...
In the conditional format, I am using Expression Is
CDate([txt_Status])<Date()

The record source is a query. I have changed the query to this to try and
force it to be in the date format.
txt_Status: Format(IIf([Completed]=False,[Due_Date]," "),"mm/dd/yyyy")

Thank you for your patience, any other suggestions is appreciated. I will
also look at multiple criteria in the expression is instead of using the
IIF
in the query.

Hi David

It seems to be interpreting the value in txt_Status as a text string, and
then doing a string comparison.

Try using the CDate function to explicitly convert the left-hand side to
a
date.

Is the control you are conditionally formatting [txt_Status]? if so then
it
should work just to say:
Field Value Is -- less than -- Date()

If it is another control on your subform, use:
Expression Is -- CDate([txt_Status]) < Date()

If it is a control on your main form, then try:
Expression Is --
CDate([sfrm_Milestones_for_Release].Form.[txt_Status])
< Date()

You should not need the entire object reference right down from the Forms
collection.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I was reading on this post about using conditional formatting.
http://msdn.microsoft.com/newsgroup...-DC48AB0DA5C9&dglist=&ptlist=&exp=&sloc=en-us

"select "Expression Is" from the
combo box, and enter a valid boolean expression. Use the entire object
specifier rather than the shortcut Me!."

I am trying to check the value of a date field in a subform.
Here is the expression I entered after selecting the field in the
subform.

Forms![frm_Milestone_Release]![sfrm_Milestones_for_Release].Form.[txt_Status]<Date()

I have also tried to select "Field is" and choosing less than and
entering
the expression Date(). Unfortunately, same result.

It seems to key off of the MM/DD and not year. For example if the date
is
12/31/06, there is no formatting. If the date is 1/1/07, it is
correctly
formatted red.

Can someone help correct my errors?