Changing Colors of fields on a report

[bill mahoney]

I have access 2k and I have 2 tables. One is a 7X6 table representing
a month with zeroes padding the fields where no day exist. For example
February 2003

0 0 0 0 0 0 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 0
0 0 0 0 0 0 0

Denotes Sunday thru Saturday.

Then I have a separate table that breaks down the month with fields
representing Start Date, End Date, Pay Date, Due Date, and Holiday as
follows

MthNum Month DateNum Year DayNum Day SDate Edate PayDate
Duedate Hol
2 February 1 2003 7 Saturday N N N N Y
N

What I am looking to do is on Dates marked with a "Y" i want to color
and bold--so for start dates I want red and bold, end dates blue and
bold, holidays underlined and so forth. The report is the first table
and I want the number to be formated as stated. Being that the columns
are shown in the detail section of the report and not the individual
fields is ther a way to selectively choose what gets colored and what
doesn't. I have the below code in my detail section

------Start Code----------
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim mOn As DAO.Recordset
Dim mOn2 As DAO.Recordset
Dim bOO As Boolean
Dim dNum(10) As Integer
Dim dNum2(10) As Integer
Dim dNum3(10) As Integer
Dim dNum4(10) As Integer
Dim dNum5(10) As Integer
Dim dNum9(15) As Integer
Dim cOunt As Integer
Dim xxx As String
Dim lngRed As Long, lngYellow As Long, lngWhite As Long
Dim lngBlue As Long, lngGreen As Long, lngMagneta As Long

lngRed = RGB(255, 0, 0)
lngblack = RGB(0, 0, 0)
lngYellow = RGB(255, 255, 0)
lngWhite = RGB(255, 255, 255)
lngGreen = RGB(0, 150, 0)
lngBlue = RGB(0, 0, 255)
lngMagneta = RGB(180, 140, 65)

Set mOn = CurrentDb.OpenRecordset("February")
bOO = False
For i = 1 To 7
If Me("day" & i) = 0 Then
Me("day" & i).Visible = False
Else
Me("day" & i).Visible = True
End If
Next i

'then do 8 minus count and fill in with zeroes the do a large if
statement
cOunt = 0
mOn.MoveFirst
Do While Not mOn.EOF
If Trim(mOn.Fields(6)) = "Y" Then
dNum(cOunt) = mOn.Fields(2)
cOunt = cOunt + 1
End If
mOn.MoveNext
Loop
'cOunt = 10 - cOunt
For cOunt = cOunt To 9
dNum(cOunt) = 99
Next cOunt
cOunt = 0
mOn.MoveFirst
Do While Not mOn.EOF
If Trim(mOn.Fields(7)) = "Y" Then
dNum2(cOunt) = mOn.Fields(2)
cOunt = cOunt + 1
End If
mOn.MoveNext
Loop
'cOunt = 10 - cOunt
For cOunt = cOunt To 9
dNum2(cOunt) = 99
Next cOunt
cOunt = 0
mOn.MoveFirst
Do While Not mOn.EOF
If Trim(mOn.Fields(8)) = "Y" Then
dNum3(cOunt) = mOn.Fields(2)
cOunt = cOunt + 1
End If
mOn.MoveNext
Loop
'cOunt = 10 - cOunt
For cOunt = cOunt To 9
dNum3(cOunt) = 99
Next cOunt
cOunt = 0
mOn.MoveFirst
Do While Not mOn.EOF
If Trim(mOn.Fields(9)) = "Y" Then
dNum4(cOunt) = mOn.Fields(2)
cOunt = cOunt + 1
End If
mOn.MoveNext
Loop
'cOunt = 10 - cOunt
For cOunt = cOunt To 9
dNum4(cOunt) = 99
Next cOunt
cOunt = 0
mOn.MoveFirst
Do While Not mOn.EOF
If Trim(mOn.Fields(10)) = "Y" Then
dNum5(cOunt) = mOn.Fields(2)
cOunt = cOunt + 1
End If
mOn.MoveNext
Loop
'cOunt = 10 - cOunt
For cOunt = cOunt To 9
dNum5(cOunt) = 99
Next cOunt
cOunt = 0
mOn.MoveFirst
Do While Not mOn.EOF
If Trim(mOn.Fields(6)) = "Y" Then
dNum9(cOunt) = mOn.Fields(2)
cOunt = cOunt + 1
End If
If Trim(mOn.Fields(7)) = "Y" Then
dNum9(cOunt) = mOn.Fields(2)
cOunt = cOunt + 1
End If
If Trim(mOn.Fields(8)) = "Y" Then
dNum9(cOunt) = mOn.Fields(2)
cOunt = cOunt + 1
End If
If Trim(mOn.Fields(9)) = "Y" Then
dNum9(cOunt) = mOn.Fields(2)
cOunt = cOunt + 1
End If
If Trim(mOn.Fields(10)) = "Y" Then
dNum9(cOunt) = mOn.Fields(2)
cOunt = cOunt + 1
End If
mOn.MoveNext
Loop
'cOunt = 10 - cOunt
For cOunt = cOunt To 14
dNum9(cOunt) = 99
Next cOunt

For i = 1 To 7
If (Me("day" & i) = dNum(0) Or Me("day" & i) = dNum(1) Or Me("day"
& i) = dNum(2) Or Me("day" & i) = dNum(3) Or Me("day" & i) = dNum(4)
Or Me("day" & i) = dNum(5) Or Me("day" & i) = dNum(6) Or Me("day" & i)
= dNum(7) Or Me("day" & i) = dNum(8) Or Me("day" & i) = dNum(9)) Then
Me("day" & i).FontUnderline = True
'Me("day" & i).FontBold = False
'Me("day" & i).ForeColor = lngblack

ElseIf Me("day" & i) = dNum2(0) Or Me("day" & i) = dNum2(1) Or
Me("day" & i) = dNum2(2) Or Me("day" & i) = dNum2(3) Or Me("day" & i)
= dNum2(4) Or Me("day" & i) = dNum2(5) Or Me("day" & i) = dNum2(6) Or
Me("day" & i) = dNum2(7) Or Me("day" & i) = dNum2(8) Or Me("day" & i)
= dNum2(9) Then
Me("day" & i).ForeColor = lngRed
Me("day" & i).FontBold = True
Me("day" & i).FontUnderline = False

ElseIf Me("day" & i) = dNum3(0) Or Me("day" & i) = dNum3(1) Or
Me("day" & i) = dNum3(2) Or Me("day" & i) = dNum3(3) Or Me("day" & i)
= dNum3(4) Or Me("day" & i) = dNum3(5) Or Me("day" & i) = dNum3(6) Or
Me("day" & i) = dNum3(7) Or Me("day" & i) = dNum3(8) Or Me("day" & i)
= dNum3(9) Then
Me("day" & i).ForeColor = lngGreen
Me("day" & i).FontBold = True

ElseIf Me("day" & i) = dNum4(0) Or Me("day" & i) = dNum4(1) Or
Me("day" & i) = dNum4(2) Or Me("day" & i) = dNum4(3) Or Me("day" & i)
= dNum4(4) Or Me("day" & i) = dNum4(5) Or Me("day" & i) = dNum4(6) Or
Me("day" & i) = dNum4(7) Or Me("day" & i) = dNum4(8) Or Me("day" & i)
= dNum4(9) Then
Me("day" & i).ForeColor = lngBlue
Me("day" & i).FontBold = True

ElseIf Me("day" & i) = dNum5(0) Or Me("day" & i) = dNum5(1) Or
Me("day" & i) = dNum5(2) Or Me("day" & i) = dNum5(3) Or Me("day" & i)
= dNum5(4) Or Me("day" & i) = dNum5(5) Or Me("day" & i) = dNum5(6) Or
Me("day" & i) = dNum5(7) Or Me("day" & i) = dNum5(8) Or Me("day" & i)
= dNum5(9) Then
Me("day" & i).ForeColor = lngMagneta
Me("day" & i).FontBold = True

'cc
ElseIf Me("day" & i) <> dNum9(0) And Me("day" & i) <> dNum9(1) And
Me("day" & i) <> dNum9(2) And Me("day" & i) <> dNum9(3) And Me("day" &
i) <> dNum9(4) And Me("day" & i) <> dNum9(5) And Me("day" & i) <>
dNum9(6) And Me("day" & i) <> dNum9(7) And Me("day" & i) <> dNum9(8)
And Me("day" & i) <> dNum9(9) And Me("day" & i) <> dNum9(10) And
Me("day" & i) <> dNum9(11) And Me("day" & i) <> dNum9(12) And Me("day"
& i) <> dNum9(13) And Me("day" & i) <> dNum9(14) Then
Me("day" & i).FontUnderline = False
Me("day" & i).FontBold = False
Me("day" & i).ForeColor = lngblack

'ElseIf Me("day" & i) <> dNum2(0) And Me("day" & i) <> dNum2(1)
And Me("day" & i) <> dNum2(2) And Me("day" & i) <> dNum2(3) And
Me("day" & i) <> dNum2(4) And Me("day" & i) <> dNum2(5) And Me("day" &
i) <> dNum2(6) And Me("day" & i) <> dNum2(7) And Me("day" & i) <>
dNum2(8) And Me("day" & i) <> dNum2(9) Then
'Me("day" & i).ForeColor = lngblack
'Me("day" & i).FontBold = False
'Me("day" & i).FontUnderline = False

'ElseIf Me("day" & i) <> dNum3(0) And Me("day" & i) <> dNum3(1)
And Me("day" & i) <> dNum3(2) And Me("day" & i) <> dNum3(3) And
Me("day" & i) <> dNum3(4) And Me("day" & i) <> dNum3(5) And Me("day" &
i) <> dNum3(6) And Me("day" & i) <> dNum3(7) And Me("day" & i) <>
dNum3(8) And Me("day" & i) <> dNum3(9) Then
'Me("day" & i).ForeColor = lngblack
'Me("day" & i).FontBold = False
'Me("day" & i).FontUnderline = False

'ElseIf Me("day" & i) <> dNum4(0) And Me("day" & i) <> dNum4(1)
And Me("day" & i) <> dNum4(2) And Me("day" & i) <> dNum4(3) And
Me("day" & i) <> dNum4(4) And Me("day" & i) <> dNum4(5) And Me("day" &
i) <> dNum4(6) And Me("day" & i) <> dNum4(7) And Me("day" & i) <>
dNum4(8) And Me("day" & i) <> dNum4(9) Then
'Me("day" & i).ForeColor = lngblack
'Me("day" & i).FontBold = False
'Me("day" & i).FontUnderline = False

'ElseIf Me("day" & i) <> dNum5(0) And Me("day" & i) <> dNum5(1)
And Me("day" & i) <> dNum5(2) And Me("day" & i) <> dNum5(3) And
Me("day" & i) <> dNum5(4) And Me("day" & i) <> dNum5(5) And Me("day" &
i) <> dNum5(6) And Me("day" & i) <> dNum5(7) And Me("day" & i) <>
dNum5(8) And Me("day" & i) <> dNum5(9) Then
'Me("day" & i).ForeColor = lngblack
'Me("day" & i).FontBold = False
'Me("day" & i).FontUnderline = False

End If
Next i
mOn.Close: Set mOn = Nothing
End Sub
-------End Code-------

It seems to work OK for the most part but there seems to be a conflict
in that the 9th and 23rd in column 1 of my first table are sart dates
and should be red abd the 16th is a holiday and should be underlined
and black--but what happens is that it is red and underlined. Is there
a way to specifically choose a row and column number in the report to
access a particular field. Thanks for any help and sorry about my
spaghetti code--i am just learning.

Regards,
Bill Mahoney
The Alcott Group