date comparison between fields, show if greater than in red

[romann]

I have a report which lists the project name and several dates as column headings which I use to track the status of projects.

I would like to compare two columns of dates and if a date in the first column [In] is greater than the second column [Out] the font in the first column switches to red.

This is the code I'm currently using:

If Me.Latest_PER_Correspondence_In > Me.Latest_PER_Correspondence_Out Then
Me.Latest_PER_Correspondence_In.ForeColor = vbRed
Me.Latest_PER_Correspondence_In.FontBold = True

Else
Me.Latest_PER_Correspondence_In.ForeColor = vbBlack

When I run the report some dates from the first column [In]which are earlier than the second column [Out] appear red. For example, in the first column the date 9/20/2006 appears red even though the second column is 10/2/06.

If it matters, the report is run off a query and the fields in the report are formulas I inserted to view the latest date of entry (i.e. Latest PER Correspondence In: Nz([PERRR3],Nz([PERRR2],Nz([PERRR 1],[PERReceived]))).

Appreciate any ideas here.

romann

 

[IGPCS]

what you mean is that the whole column get red? including the fields that are erlier or some are black as well?

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[dhookom]

You should try removing your code and use conditional formatting. Also, I expect your comparisons are treating your fields as strings so 10/2/2006 comes before 9/20/2006. You might need to convert the values back to dates with DateValue().


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[romann]

Duane,

I suspected fields were treated as strings so where/how do I convert the values back to dates with Date Value() on the report?

Thanks.
romann

 

[dhookom]

Probably convert at the source:
LatestPERCorrespondenceIn: DateValue(Nz([PERRR3],Nz([PERRR2],Nz([PERRR 1],[PERReceived]))))

I consider PERRR1, PERRR2, PERRR3 as being un-normalized but I could be wrong.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[romann]

Can't seem to get that to work. There are still certain dates that appear red when they shouldn't be. Additionally, when I converted to date value if there is nothing in the field there is an Error where nothing used to be.

Where would I check to make sure PERRR1,etc. are un-normalized?

 

[dhookom]

If it is possible that all fields are null you may have an issue without providing a default "if all null" date.

Can you provide sample dates that appear red when they shouldn't? Is there a pattern that you can share?

I don't know what or why you have multiple fields with similar names. I would expect to see multiple records in a related table.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]