Data field color in report based on date

[Mike Hughes]

I have a report which lists the "date of application" and "today’s date" and then a calculated field which is the "number of days" between the application and today’s date.

Is there a way to have the program change the color of the data in the "number of days" field to a different color when the number of days is over, say 60 days, 120 days,etc. and can this be set to different colors based on the number of days?

Like 60 days RED, 120 green, etc

 

[bjackson]

use the on print event for your report and a case select

dim NoOfDays as single
NoOfdays=me![the name of the calculated control]

select case NoOfDays

case 1 to 10
me![the name of the calculated control].forcolor=vbred
case 10.1 to 20
me![the name of the calculated control].forecolor=vbblack
case 20.1 to 30
etc etc

case else
do nothing
end select

 

[Mike Hughes]

I real new to this and I don't understand your directions. Would it be possible to go into more detail for me. Thanks

 

[bjackson]

in design view of your report

1. your report has a section called detail.right click anywhere
in that detail area or on the bar that goes across the screen
with the word "Detail" on it

2.select properties.Its at the end of the drop down menu

3.select the tab marked "Events"

4.it has 3 things.on format, on print,on retreat

click on "on print" and start to type [

5.You should now have [Event procedure]
6.click on the 3 small dots on the rhs of event prodecure

7.you are now in the code module for your report

8 paste the code between the two blocks of writing
the only thing in the code to change are the names
of the controls as i dont know what they are and
change numbers to suit what day range you want
to use


dim NoOfDays as single
NoOfdays=me![the name of the calculated control]

select case NoOfDays

case 1 to 10
me![the name of the calculated control].forcolor=vbred
case 10.1 to 20
me![the name of the calculated control].forecolor=vbblack
case 20.1 to 30
etc etc

case else
do nothing
end select

on the top toolbar select debug

then select compile and save all modules
if their are no syntax problems then you have just written
your first bit of code

close the module and save

keep asking questions if you are not sure as all the
people who use these forums know exactly where your
coming from

hope this helps

 

[Mike Hughes]

I get this error when I run the debugger:

Compile error:
Expected Sub, Function, or Property
And the first “etc” is highlighted in blue

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim NoOfDays As Single
NoOfDays = Me![Number of Days Old]

Select Case NoOfDays

Case 1 To 10
Me![Number of Days Old].forcolor = vbRed
Case 10.1 To 20
Me![Number of Days Old].ForeColor = vbBlack
Case 20.1 To 30
etc etc

Case Else
do nothing
End Select

End Sub

Can you help ?

 

[bjackson]

sorry mike

i missed that, just delete etc etc i put it there
only as an example for you to add
more cases if you wanted to

you know if you want to change the amount of
days then you just change the numbers

e.g. case 100 to 200 would give you
a number range >=100 and <=200
so if the no of days falls between these numbers
then it would change your textbox forecolor

case 20,30,40,50
this would change your textbox colour if
no of days =20 or 30 or 40 or 50

case 120
this would change your text box color only
if the no of days =120

 

[Mike Hughes]

Your must think I'm stupid...but I guess I have to learn.
Now I get a complile error on the "do nothing " line.

And I don't understand where to change the amount of time??
Do I put the date range in the line "Case 1 TO 10" to change the color to red, or do I put something in between the next line with the brackets "Me![Number of Days Old].forcolor=vbRed"?

Thanks again. Mike




Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim NoOfDays As Single
NoOfDays = Me![Number of Days Old]

Select Case NoOfDays

Case 1 To 10
Me![Number of Days Old].forcolor = vbRed
Case 10.1 To 20
Me![Number of Days Old].ForeColor = vbBlack
Case 20.1 To 30

Case Else
do nothing
End Select

End Sub

 

[bjackson]

no i dont,your where i was 3 years ago

delete the words do nothing

you change the actual numbers



if you wanted your colour change to happen
when your no of days was between 1 and 10
case 1 to 10

if you wanted your colour change to happen
when your no of days was between 10 and 20
case 10 to 20

if you wanted your colour change to happen
when your no of days was between 119 and 120
case 119 to 120

if you wanted your colour change to happen
when your no of days was exactly 120

case 120

if you wanted your colour change to happen
when your no of days was exactly 60

case 60

if you wanted your colour change to happen
when your no of days was greater than 120 and less than
100000

case 120 to 100000

dont worry about asking questions,we all have to
start somewhere

im not quite sure what you mean by date range
i thought you had a caculated field on which you
wanted to change the colour and this field is a number
not a date

 

[Mike Hughes]

I get a runtime error: 438
Object doesn't support this property or method

The line "Me![Number Of Days Old].forcolor=vbRed
is highlighted in yellow



Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim NoOfDays As Single
NoOfDays = Me![Number Of Days Old]

Select Case NoOfDays

Case 1 To 10
Me![Number Of Days Old].forcolor = vbRed
Case 10.1 To 20
Me![Number Of Days Old].ForeColor = vbBlack
Case 20.1 To 30

Case Else
End Select

End Sub
Me![Number of Days Old].forcolor=vbRed Me![Number Of Days Old].forcolor = vbRed

 

[bjackson]

forcolor is spelt wrong
access is very particular with spelling

change it to ForeColor

you must be getting ready to throw something

 

[Mike Hughes]

Well I was, but I went to work and took it out on everyone there!

This worked! Thanks.....one more thing? (I know you expected more so) Is there a way to add a bold command to that also, so the number will be red and bold!

If it can't be done or is too hard, no problem. Thanks again for your time - I hope your around when I have more questions, and I'm sure that I will.

Mike

 

[bjackson]

i apologize for not testing the code before i sent it
to you, but it is a good way of learning if you can stand
the frustration
it will get easier
again you set your font depending on what case you
require

if you want bold for cases 1 to 10 then add a line of code
Case 1 To 10
Me![Number of Days Old].forecolor = vbRed
Me![Number of Days Old].fontbold= true


Case 10.1 To 20
Me![Number of Days Old].ForeColor = vbBlack
Me![Number of Days Old].fontbold= false



this is where i would use a case else

case else
Me![Number of Days Old].forecolor = vbblack
Me![Number of Days Old].fontbold= false


that way you are effectively setting a default

just on colours you can put a color number
instead of vbred or vbblack etc

eg:Me![Number of Days Old].ForeColor = 255

if you want to know what the color number is just
set the colour in design view and copy and paste its number

 

[Mike Hughes]

Works Great! Thanks

 

[Mike Hughes]

bjackson or anyone else that can help

Talked too soon.

I have added another report to the database and when I click on the preview button I get a runtime error 94 - Invalid use of Null

When I debug the "NoOfDays=Me![Time in days] is highlighted in yellow....
when I run the mouse over the line I get
NoOfDays=0

What did I do wrong this time?



Option Compare Database


Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim NoOfDays As Single
NoOfDays = Me![Time in Days]

Select Case NoOfDays

Case 1 To 10
Me![Time in Days].ForeColor = vbRed
Case 10.1 To 20
Me![Time in Days].ForeColor = vbBlack
Case 20.1 To 30

Case Else
End Select

End Sub

NoOfDays = Me![Time in Days]

 

[Mike Hughes]

correction:

bjackson or anyone else that can help

Talked too soon.

I have added another report to the database and when I click on the preview button I get a runtime error 94 - Invalid use of Null

When I debug the "NoOfDays=Me![Time in days] is highlighted in yellow....
when I run the mouse over the line I get
NoOfDays=0

What did I do wrong this time?



Option Compare Database


Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim NoOfDays As Single
NoOfDays = Me![Time in Days]

Select Case NoOfDays

Case 1 To 10
Me![Time in Days].ForeColor = vbRed
Case 10.1 To 20
Me![Time in Days].ForeColor = vbBlack
Case 20.1 To 30

Case Else
End Select

End Sub

 

[bjackson]

me![time in days] must have a null value,
check to see that all ![time in days] has a value,or try
the code below,which will give NoOfDays a Value
if ![time in days] is null,or you could which i dont like
doing ,replace
Dim NoOfDays As Single with
dim NoOfDays as Variant

also in any code module you have code in
add the words Option Explicit

Option Compare Database
option Explicit

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim NoOfDays As Single

if isnull(me![Time In Days]) then
NoOfDays=0 ' or any other value you may want to give it
else
NoOfDays = Me![Time in Days]
end if

Select Case NoOfDays

Case 1 To 10
Me![Time in Days].ForeColor = vbRed
Case 10.1 To 20
Me![Time in Days].ForeColor = vbBlack
Case 20.1 To 30

Case Else
End Select

End Sub

 

[Mike Hughes]

Old Thread new question

I used this code sometime ago.

Is there something that I can do to adjust it to change the color of a date when the date is 45 days less than todays date?

The final code I ended up with was this:

Option Compare Database
option Explicit

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim NoOfDays As Single

if isnull(me![Time In Days]) then
NoOfDays=0 ' or any other value you may want to give it
else
NoOfDays = Me![Time in Days]
end if

Select Case NoOfDays

Case 1 To 10
Me![Time in Days].ForeColor = vbRed
Case 10.1 To 20
Me![Time in Days].ForeColor = vbBlack
Case 20.1 To 30

Case Else
End Select

End Sub

 

[bjackson]

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim NoOfDays As Single

if isnull(me![Time In Days]) then
NoOfDays=0 ' or any other value you may want to give it
else
NoOfDays = Me![Time in Days]
end if

Select Case NoOfDays

Case 1 To 10
Me![Time in Days].ForeColor = vbRed
Case 10.1 To 20
Me![Time in Days].ForeColor = vbBlack
Case 20.1 To 30

Case Else
End Select

if me![Time In Days]) > dateadd("d",-45,me![Time In Days]) then
Me![Time in Days].ForeColor = vbRed ' or any colour you want
Me![Time in Days].backColor = vbwhite ' etc
else
Me![Time in Days].ForeColor = vbwhite
end if


End Sub