Conditional Formatting, VBA, lions and bears!

[1quickst162]

I'm sure this has been answered (matter of fact I'm still scouring the forums trying to find an example or the answer to what I'm looking for).

Access 2003 as we all know has a limit of 3 conditional formatting options (4 if you count the default). I'm looking to add to this via some VBA code. The data I'm referencing is in a table stored in columns labeled 'Day1, Day2' all the way to 31 (for days of the month). Each row has a persons name (for example John Doe). This is sorta like a time clock entry. John Doe did 3 hours of work on the 3rd day, so in Day3 it'd have 3.0.

Anyway, the report that was given to me pulls this data and generates the report. Right now, if it's over 6.5 the cell is colored Green. between 3.0 and 6.5 it's another color, less than 3.0 is another color and if no data entered it's red (default). I want to break this down even further.

I'm having a hard time finding the proper steps to do this. Would I need to do it as Report_Open or Report_Load function? Module? ARGH.

I can do this no problem in Excel, I just can't figure out the VBA to make it work in Access 2003.

Thanks.

 

[AccessMSSQL]

You would probably have to do it on the Report Detail Format or Print event to check each row as the report is generated.

 

[1quickst162]

Thanks for the response. Another question would be when I'm building the If/Then statements, do I need to reference the textboxes in the report, or the Columns from the table (ie. If Me.Textbox1 or Me.Day1).

 

[1quickst162]

Well for the most part, I've figured it out. Here's the code I am using

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    Select Case Me.tisort
        Case "10"
            Select Case Text172
                Case 0 To 4.9:
                    SetBurntOrange Text172
                Case 5 To 5.9:
                    SetYellow Text172
                Case 6 To 6.9:
                    SetCyan Text172
                Case Is >= 7:
                    SetGreen Text172
             End Select
    End Select
End Sub

So far, everything works. The problem I'm running into now though is some cells are blank. In doing this it formats whatever the previous cell was. The guy I'm making this for said that by default, all cells will be RED if they're empty by default. How can I specify to set to Red for an empty cell?

I've tried the Case = "", Case ISNull, case empty, etc.

 

[boblarson]

How about

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    Select Case Me.tisort
        Case "10"
            Select Case Text172
                Case 0 To 4.9:
                    SetBurntOrange Text172
                Case 5 To 5.9:
                    SetYellow Text172
                Case 6 To 6.9:
                    SetCyan Text172
                Case Is >= 7:
                    SetGreen Text172
[B][COLOR=red]                 Case Else[/COLOR][/B]
[B][COLOR=red]                   ...do your stuff here[/COLOR][/B]
             End Select
    End Select
End Sub

 

[1quickst162]

Thanks boblarson, that was the problem. (I'm not the best in VBA, but with google and how-tos I figured it out). Now this is more of a design question I guess, what would be the best way to achieve the following?

Current setup:
Report fields has 31 text boxes, each one cooresponds with day of the month (day1-31).
Beginning of each month, someone goes in and changes non-workdays (holidays and weekends) to a set color no matter what the amount is in the sell.


Desired:
Make the report 'idiot' proof and elimiate the need for someone to go in and change it each month for weekends or holidays.


Options I've come up with:
1. Create 12 copies of the report. Set up each box according to the day (weekend, holiday, etc) for each month.

2. Use the date functions within the VBA I 've written.

Ideally I'd like to use #2 for obvious reasons. Problem being the method I'd use in excel won't work in access. Based upon information located here:
DUB-DUB-DUB.vb6.us/tutorials/date-time-functions-visual-basic

I think it would be possible. (Forgive me I'm using this somewhat as a brain dump). Using the WeekDay function, I can do something like:

Private Sub (something)
If Weekday is >=2 and <=6 then do my formatting above
    else do this formattin
End Sub

Clearly that's not the correct syntax, just the laymens term. I'm trying to figure out how to use the Date function to get this worked out. Or am I just dreaming?

 

[gemma-the-husky]

close, just slightly off. Also i tend to use vb constants rather than day numbers.

if weekday(somedate) >=vbmonday or weekday(somedate)<=vbthursday