Changing text color in a report on a certain Date

[mkopsick]

Ok Access guru's i need your help once again! I have a report that shows a suspense date in it. This is what im trying to do, we have three selections we use on the form when we input the dates. the first is "1 Day" which i need to turn red instantly, the second choice is "4 Days" and i need the date to turn red on the third day and "10 Days" in which i need the date to turn red on the eighth day. so basically we enter dates daily and i need something that keeps track of the date we enter and changes the color of the text on the report on the days shown above. When the days are enter in the form its a drop down menu that lets your select 1 Day, 4 Days, and Ten Days. it takes what you select and adds that many days to todays date and then places it in a table. We run the report daily as well. im a novice vb programmer and im kinda stumped on which way to persue this. Your help would be greatly appreciated.

 

[pbaldy]

The simplest way is probably Format/Conditional Formatting (on the ribbon in 2007). You'd use Expression Is, and probably the DateDiff function along with the Date() function to get today's date. More info on those in Help.

 

[mkopsick]

appreciate the reply, but like i said im a novice....i have the date part working and the result stored in a table....so do i use the functions above on the form side or the report side?

 

[mkopsick]

is there a way to run something on the table and have the text color changed in that field based on a date it reaches?

 

[pbaldy]

Conditional Formatting is available in both forms and reports. I'm not clear on exactly what you have stored. If you already have the date stored, then you just need to compare that to Date().

 

[mkopsick]

i used this in my form to store the dates...

Private Sub Combo36_AfterUpdate()
Select Case Me![Susp Days]
Case "1 Day"
Me![Susp Act Date] = Date
Case "4 Days"
Me![Susp Act Date] = DateAdd("d", 4, Date)
Case "10 Days"
Me![Susp Act Date] = DateAdd("d", 10, Date)
End Select
End Sub

the field in the table is "susp act date" where would u suggest i start?
everyday there will be different dates added and somehow i need for it to look at the date in the field and change the date color with what i discibed above

 

[mkopsick]

how could i write the expression to use in the condition to compare the dates?

I.E. 05/27/2009 and lets say its the 4 days susp date i need it to turn red on 05/31/2009 and stay that way.....help?

 

[pbaldy]

Try this on the control with [Susp Act Date]:

Field Value Is/Less Than/Date()

 

[mkopsick]

well one more thing..i want if its "1 day" to turn red instantly, if its "4 days" i want it to turn red on the third day, and if its "ten days" i want it red on the eight day. i really appreciate your help...you guys are the Best

 

[mkopsick]

i also have a field in the table that says 1 day, 4 days and ten days that colaberate the date

 

[mkopsick]

i have three fields to work with "rec'd date", which is the current date the record was entered, i have "susp days" which puts in the number of days i.e. 1 Day, 4 Days, or 10 Days, and the "susp act date" which has the date in it according to how many days were selected for the suspense. so i need to compare the susp days to the susp act date? trying to find starting place

 

[pbaldy]

I thought that the susp acct date field already represented the number of days after (1,4,10), so that could be used for this.

 

[mkopsick]

it does but we enter dates everyday...so i have to compare the rec'd date to the susp act date to determine if its 1,4,10 or i have a field that says 1 day, 4 days, or ten days and based on that and the susp act date determine what day to turn the text red....am i making sense to you?

 

[mkopsick]

wouldnt i use if then
for one day if "susp days" = "1 days" then "susp act date" turns red.
for four days if "susp days" = "4 days" then subtract one day from "susp act date" and turn red.
for ten days if "susp days" = "10 days" then subtract two days from "susp act date" and turn red.

 

[mkopsick]

i want the 4 day one to turn red in three days and the 10 day one to turn red in 8 days so i know there coming due make sense at all

 

[mkopsick]

is there a timer function?

 

[mkopsick]

i did the condition for the 1 day would i use a datediff like this for the 4 days? If DateDiff("d", susp act date, date()) <= 3 Then

trying

 

[pbaldy]

Yes, though you'd have to bracket the field name (and use Expression Is).

DateDiff("d", [susp act date], date()) <= 3

 

[mkopsick]

im doing this in a report, i have the first condition [susp days]="1 day" where im using a different field that has "1 day" to change the color

the second condition i have DateDiff("d",[susp act date],Date())<=3 and nothings happening

 

[mkopsick]

can i do this? [susp days]="4 days", DateDiff("d",[susp act date],Date())<=3