creating "map" based on field values

I have a report I would like to convert into a kind of map. I'm trying to do
this by creating labels that correspond to particular sections. If acreage in
a section meets certain criteria, the label would be colored accordingly. The
labels are arranged contiguously into a map.
However, it's not working. Following is a sample of the code I'm using for
an event procedure under the format event for the group footer for each STR
(made up of 36 sections.) But all my "maps" look the same and do not reflect
any of the data.
Here's the code:

If Me.[S] = "01" And Me.Status = "In-Hand" And Me.PCT 0.75 Then
L1.BackColor = 16744576
ElseIf Me.[S] = "01" And Me.Status = "Leased to other" And Me.PCT 0.75
Then
L1.BackColor = 255
Else: L1.BackColor = 16777215
End If

If Me.[S] = "02" And Me.Status = "In-Hand" And Me.PCT 0.75 Then
L1.BackColor = 16744576
ElseIf Me.[S] = "02" And Me.Status = "Leased to other" And Me.PCT 0.75
Then
L1.BackColor = 255
Else: L2.BackColor = 16777215
End If

S (section), status and percent are all fields in the detail section of the
report. It didn't matter if I moved the map to the detail section - it was
still inaccurate.
Should I simply set up a function in a query to create a new field
representing the section status? Also, there are 36 sections and I would
really like to simplify the code - I assume there's an easy way with an
array. Could you point me in the right direction on that, too.
Thanks

In your second If loop, shouldn't L1 be L2?

This could all be set up in code in a Do loop rather than a series of 36 If
loops.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications




"okschlaps" wrote in message
...
I have a report I would like to convert into a kind of map. I'm trying to
do
this by creating labels that correspond to particular sections. If acreage
in
a section meets certain criteria, the label would be colored accordingly.
The
labels are arranged contiguously into a map.
However, it's not working. Following is a sample of the code I'm using for
an event procedure under the format event for the group footer for each
STR
(made up of 36 sections.) But all my "maps" look the same and do not
reflect
any of the data.
Here's the code:

If Me.[S] = "01" And Me.Status = "In-Hand" And Me.PCT 0.75 Then
L1.BackColor = 16744576
ElseIf Me.[S] = "01" And Me.Status = "Leased to other" And Me.PCT
0.75
Then
L1.BackColor = 255
Else: L1.BackColor = 16777215
End If

If Me.[S] = "02" And Me.Status = "In-Hand" And Me.PCT 0.75 Then
L1.BackColor = 16744576
ElseIf Me.[S] = "02" And Me.Status = "Leased to other" And Me.PCT
0.75
Then
L1.BackColor = 255
Else: L2.BackColor = 16777215
End If

S (section), status and percent are all fields in the detail section of
the
report. It didn't matter if I moved the map to the detail section - it was
still inaccurate.
Should I simply set up a function in a query to create a new field
representing the section status? Also, there are 36 sections and I would
really like to simplify the code - I assume there's an easy way with an
array. Could you point me in the right direction on that, too.
Thanks

As Steve suggested, you might be able to change all your code to something
like:
Dim intS as Integer
Dim strS as String
For intS = 1 to 36
strS = Format(intS,"00")
If Me.[S] = strS And Me.Status = "In-Hand" And Me.PCT 0.75 Then
Me("L" & intS).BackColor = 16744576
ElseIf Me.[S] = strS And Me.Status = "Leased to other" And Me.PCT 0.75
Me("L" & intS).BackColor = 255
Else
Me("L" & intS).BackColor = 16777215
End If
Next

It isn't clear what your records look like. We understand something doesn't
work but we don't know much more.
--
Duane Hookom
Microsoft Access MVP


"Steve" wrote:

In your second If loop, shouldn't L1 be L2?

This could all be set up in code in a Do loop rather than a series of 36 If
loops.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications




"okschlaps" wrote in message
...
I have a report I would like to convert into a kind of map. I'm trying to
do
this by creating labels that correspond to particular sections. If acreage
in
a section meets certain criteria, the label would be colored accordingly.
The
labels are arranged contiguously into a map.
However, it's not working. Following is a sample of the code I'm using for
an event procedure under the format event for the group footer for each
STR
(made up of 36 sections.) But all my "maps" look the same and do not
reflect
any of the data.
Here's the code:

If Me.[S] = "01" And Me.Status = "In-Hand" And Me.PCT 0.75 Then
L1.BackColor = 16744576
ElseIf Me.[S] = "01" And Me.Status = "Leased to other" And Me.PCT
0.75
Then
L1.BackColor = 255
Else: L1.BackColor = 16777215
End If

If Me.[S] = "02" And Me.Status = "In-Hand" And Me.PCT 0.75 Then
L1.BackColor = 16744576
ElseIf Me.[S] = "02" And Me.Status = "Leased to other" And Me.PCT
0.75
Then
L1.BackColor = 255
Else: L2.BackColor = 16777215
End If

S (section), status and percent are all fields in the detail section of
the
report. It didn't matter if I moved the map to the detail section - it was
still inaccurate.
Should I simply set up a function in a query to create a new field
representing the section status? Also, there are 36 sections and I would
really like to simplify the code - I assume there's an easy way with an
array. Could you point me in the right direction on that, too.
Thanks

Perfect! That code is much prettier. Plus, I don't know why, but it's making
my maps work. Before each map returned the same incorrect data, but now they
are accurate.
Thanks for your help!

"Duane Hookom" wrote:

As Steve suggested, you might be able to change all your code to something
like:
Dim intS as Integer
Dim strS as String
For intS = 1 to 36
strS = Format(intS,"00")
If Me.[S] = strS And Me.Status = "In-Hand" And Me.PCT 0.75 Then
Me("L" & intS).BackColor = 16744576
ElseIf Me.[S] = strS And Me.Status = "Leased to other" And Me.PCT 0.75
Me("L" & intS).BackColor = 255
Else
Me("L" & intS).BackColor = 16777215
End If
Next

It isn't clear what your records look like. We understand something doesn't
work but we don't know much more.
--
Duane Hookom
Microsoft Access MVP


"Steve" wrote:

In your second If loop, shouldn't L1 be L2?

This could all be set up in code in a Do loop rather than a series of 36 If
loops.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications




"okschlaps" wrote in message
...
I have a report I would like to convert into a kind of map. I'm trying to
do
this by creating labels that correspond to particular sections. If acreage
in
a section meets certain criteria, the label would be colored accordingly.
The
labels are arranged contiguously into a map.
However, it's not working. Following is a sample of the code I'm using for
an event procedure under the format event for the group footer for each
STR
(made up of 36 sections.) But all my "maps" look the same and do not
reflect
any of the data.
Here's the code:

If Me.[S] = "01" And Me.Status = "In-Hand" And Me.PCT 0.75 Then
L1.BackColor = 16744576
ElseIf Me.[S] = "01" And Me.Status = "Leased to other" And Me.PCT
0.75
Then
L1.BackColor = 255
Else: L1.BackColor = 16777215
End If

If Me.[S] = "02" And Me.Status = "In-Hand" And Me.PCT 0.75 Then
L1.BackColor = 16744576
ElseIf Me.[S] = "02" And Me.Status = "Leased to other" And Me.PCT
0.75
Then
L1.BackColor = 255
Else: L2.BackColor = 16777215
End If

S (section), status and percent are all fields in the detail section of
the
report. It didn't matter if I moved the map to the detail section - it was
still inaccurate.
Should I simply set up a function in a query to create a new field
representing the section status? Also, there are 36 sections and I would
really like to simplify the code - I assume there's an easy way with an
array. Could you point me in the right direction on that, too.
Thanks

I spoke too soon. The code runs, but only the last label is colored in. For
instance, in one group, there are three sections that meet the criteria, but
only the last is colored in. Are all labels reformatted according to the last
"for" in the loop?

"Duane Hookom" wrote:

As Steve suggested, you might be able to change all your code to something
like:
Dim intS as Integer
Dim strS as String
For intS = 1 to 36
strS = Format(intS,"00")
If Me.[S] = strS And Me.Status = "In-Hand" And Me.PCT 0.75 Then
Me("L" & intS).BackColor = 16744576
ElseIf Me.[S] = strS And Me.Status = "Leased to other" And Me.PCT 0.75
Me("L" & intS).BackColor = 255
Else
Me("L" & intS).BackColor = 16777215
End If
Next

It isn't clear what your records look like. We understand something doesn't
work but we don't know much more.
--
Duane Hookom
Microsoft Access MVP


"Steve" wrote:

In your second If loop, shouldn't L1 be L2?

This could all be set up in code in a Do loop rather than a series of 36 If
loops.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications




"okschlaps" wrote in message
...
I have a report I would like to convert into a kind of map. I'm trying to
do
this by creating labels that correspond to particular sections. If acreage
in
a section meets certain criteria, the label would be colored accordingly.
The
labels are arranged contiguously into a map.
However, it's not working. Following is a sample of the code I'm using for
an event procedure under the format event for the group footer for each
STR
(made up of 36 sections.) But all my "maps" look the same and do not
reflect
any of the data.
Here's the code:

If Me.[S] = "01" And Me.Status = "In-Hand" And Me.PCT 0.75 Then
L1.BackColor = 16744576
ElseIf Me.[S] = "01" And Me.Status = "Leased to other" And Me.PCT
0.75
Then
L1.BackColor = 255
Else: L1.BackColor = 16777215
End If

If Me.[S] = "02" And Me.Status = "In-Hand" And Me.PCT 0.75 Then
L1.BackColor = 16744576
ElseIf Me.[S] = "02" And Me.Status = "Leased to other" And Me.PCT
0.75
Then
L1.BackColor = 255
Else: L2.BackColor = 16777215
End If

S (section), status and percent are all fields in the detail section of
the
report. It didn't matter if I moved the map to the detail section - it was
still inaccurate.
Should I simply set up a function in a query to create a new field
representing the section status? Also, there are 36 sections and I would
really like to simplify the code - I assume there's an easy way with an
array. Could you point me in the right direction on that, too.
Thanks

Here is the second If loop in your original post --
If Me.[S] = "02" And Me.Status = "In-Hand" And Me.PCT 0.75 Then
L1.BackColor = 16744576
ElseIf Me.[S] = "02" And Me.Status = "Leased to other" And Me.PCT 0.75
Then
L1.BackColor = 255
Else: L2.BackColor = 16777215
End If

Note in the If and ElseIf statements you try to change the BackColor of L1
but in the Else statement you try and change the BackColor of L2. I asked
you in my post about this. Duane's code differs from your code in that his
code looks at the properties of Section 02 and for ALL conditions tries to
change the BackColor of L2.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications






"okschlaps" wrote in message
...
I spoke too soon. The code runs, but only the last label is colored in. For
instance, in one group, there are three sections that meet the criteria,
but
only the last is colored in. Are all labels reformatted according to the
last
"for" in the loop?

"Duane Hookom" wrote:

As Steve suggested, you might be able to change all your code to
something
like:
Dim intS as Integer
Dim strS as String
For intS = 1 to 36
strS = Format(intS,"00")
If Me.[S] = strS And Me.Status = "In-Hand" And Me.PCT 0.75 Then
Me("L" & intS).BackColor = 16744576
ElseIf Me.[S] = strS And Me.Status = "Leased to other" And Me.PCT
0.75
Me("L" & intS).BackColor = 255
Else
Me("L" & intS).BackColor = 16777215
End If
Next

It isn't clear what your records look like. We understand something
doesn't
work but we don't know much more.
--
Duane Hookom
Microsoft Access MVP


"Steve" wrote:

In your second If loop, shouldn't L1 be L2?

This could all be set up in code in a Do loop rather than a series of
36 If
loops.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications




"okschlaps" wrote in message
...
I have a report I would like to convert into a kind of map. I'm trying
to
do
this by creating labels that correspond to particular sections. If
acreage
in
a section meets certain criteria, the label would be colored
accordingly.
The
labels are arranged contiguously into a map.
However, it's not working. Following is a sample of the code I'm
using for
an event procedure under the format event for the group footer for
each
STR
(made up of 36 sections.) But all my "maps" look the same and do not
reflect
any of the data.
Here's the code:

If Me.[S] = "01" And Me.Status = "In-Hand" And Me.PCT 0.75 Then
L1.BackColor = 16744576
ElseIf Me.[S] = "01" And Me.Status = "Leased to other" And Me.PCT

0.75
Then
L1.BackColor = 255
Else: L1.BackColor = 16777215
End If

If Me.[S] = "02" And Me.Status = "In-Hand" And Me.PCT 0.75 Then
L1.BackColor = 16744576
ElseIf Me.[S] = "02" And Me.Status = "Leased to other" And Me.PCT

0.75
Then
L1.BackColor = 255
Else: L2.BackColor = 16777215
End If

S (section), status and percent are all fields in the detail section
of
the
report. It didn't matter if I moved the map to the detail section -
it was
still inaccurate.
Should I simply set up a function in a query to create a new field
representing the section status? Also, there are 36 sections and I
would
really like to simplify the code - I assume there's an easy way with
an
array. Could you point me in the right direction on that, too.
Thanks

As per my previous post: "It isn't clear what your records look like. We
understand something doesn't work but we don't know much more."

Apparently your code is in a group section while the controls are in
another. I would expect the issues you are experiencing. If we knew your
structure and what you wanted to accomplish, we might be able to provide some
assistance.

--
Duane Hookom
Microsoft Access MVP


"okschlaps" wrote:

I spoke too soon. The code runs, but only the last label is colored in. For
instance, in one group, there are three sections that meet the criteria, but
only the last is colored in. Are all labels reformatted according to the last
"for" in the loop?

"Duane Hookom" wrote:

As Steve suggested, you might be able to change all your code to something
like:
Dim intS as Integer
Dim strS as String
For intS = 1 to 36
strS = Format(intS,"00")
If Me.[S] = strS And Me.Status = "In-Hand" And Me.PCT 0.75 Then
Me("L" & intS).BackColor = 16744576
ElseIf Me.[S] = strS And Me.Status = "Leased to other" And Me.PCT 0.75
Me("L" & intS).BackColor = 255
Else
Me("L" & intS).BackColor = 16777215
End If
Next

It isn't clear what your records look like. We understand something doesn't
work but we don't know much more.
--
Duane Hookom
Microsoft Access MVP


"Steve" wrote:

In your second If loop, shouldn't L1 be L2?

This could all be set up in code in a Do loop rather than a series of 36 If
loops.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications




"okschlaps" wrote in message
...
I have a report I would like to convert into a kind of map. I'm trying to
do
this by creating labels that correspond to particular sections. If acreage
in
a section meets certain criteria, the label would be colored accordingly.
The
labels are arranged contiguously into a map.
However, it's not working. Following is a sample of the code I'm using for
an event procedure under the format event for the group footer for each
STR
(made up of 36 sections.) But all my "maps" look the same and do not
reflect
any of the data.
Here's the code:

If Me.[S] = "01" And Me.Status = "In-Hand" And Me.PCT 0.75 Then
L1.BackColor = 16744576
ElseIf Me.[S] = "01" And Me.Status = "Leased to other" And Me.PCT
0.75
Then
L1.BackColor = 255
Else: L1.BackColor = 16777215
End If

If Me.[S] = "02" And Me.Status = "In-Hand" And Me.PCT 0.75 Then
L1.BackColor = 16744576
ElseIf Me.[S] = "02" And Me.Status = "Leased to other" And Me.PCT
0.75
Then
L1.BackColor = 255
Else: L2.BackColor = 16777215
End If

S (section), status and percent are all fields in the detail section of
the
report. It didn't matter if I moved the map to the detail section - it was
still inaccurate.
Should I simply set up a function in a query to create a new field
representing the section status? Also, there are 36 sections and I would
really like to simplify the code - I assume there's an easy way with an
array. Could you point me in the right direction on that, too.
Thanks

Steve
Yeah, Duane was right, the code should have been for L2 in the second loop
(then L3 in the third, etc.) Each Label corresponds to a Section. I have them
laid out in a grid (it all has to do with land work.)
I suspect Duane is right, that because my data are in the detail section and
the "map" (labels) are in the group footer, it's not processing correctly. I
tried to do a print screen of my report, but can't paste it in here.
I'll try to clarify. There are 36 sections. I need each section represented
on a map, or grid and am using color to indicate the status of that
particular section according to the criteria specified in the code.
So the map looks something like
6 5 4 3 2 1
7 8 9 10 11 12
18 17 16 15 14 13
etc
Each number would be in a label and I'm trying to color these.
Hope that helps and I really appreciate your efforts!

"Steve" wrote:

Here is the second If loop in your original post --
If Me.[S] = "02" And Me.Status = "In-Hand" And Me.PCT 0.75 Then
L1.BackColor = 16744576
ElseIf Me.[S] = "02" And Me.Status = "Leased to other" And Me.PCT 0.75
Then
L1.BackColor = 255
Else: L2.BackColor = 16777215
End If

Note in the If and ElseIf statements you try to change the BackColor of L1
but in the Else statement you try and change the BackColor of L2. I asked
you in my post about this. Duane's code differs from your code in that his
code looks at the properties of Section 02 and for ALL conditions tries to
change the BackColor of L2.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications






"okschlaps" wrote in message
...
I spoke too soon. The code runs, but only the last label is colored in. For
instance, in one group, there are three sections that meet the criteria,
but
only the last is colored in. Are all labels reformatted according to the
last
"for" in the loop?

"Duane Hookom" wrote:

As Steve suggested, you might be able to change all your code to
something
like:
Dim intS as Integer
Dim strS as String
For intS = 1 to 36
strS = Format(intS,"00")
If Me.[S] = strS And Me.Status = "In-Hand" And Me.PCT 0.75 Then
Me("L" & intS).BackColor = 16744576
ElseIf Me.[S] = strS And Me.Status = "Leased to other" And Me.PCT
0.75
Me("L" & intS).BackColor = 255
Else
Me("L" & intS).BackColor = 16777215
End If
Next

It isn't clear what your records look like. We understand something
doesn't
work but we don't know much more.
--
Duane Hookom
Microsoft Access MVP


"Steve" wrote:

In your second If loop, shouldn't L1 be L2?

This could all be set up in code in a Do loop rather than a series of
36 If
loops.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications




"okschlaps" wrote in message
...
I have a report I would like to convert into a kind of map. I'm trying
to
do
this by creating labels that correspond to particular sections. If
acreage
in
a section meets certain criteria, the label would be colored
accordingly.
The
labels are arranged contiguously into a map.
However, it's not working. Following is a sample of the code I'm
using for
an event procedure under the format event for the group footer for
each
STR
(made up of 36 sections.) But all my "maps" look the same and do not
reflect
any of the data.
Here's the code:

If Me.[S] = "01" And Me.Status = "In-Hand" And Me.PCT 0.75 Then
L1.BackColor = 16744576
ElseIf Me.[S] = "01" And Me.Status = "Leased to other" And Me.PCT

0.75
Then
L1.BackColor = 255
Else: L1.BackColor = 16777215
End If

If Me.[S] = "02" And Me.Status = "In-Hand" And Me.PCT 0.75 Then
L1.BackColor = 16744576
ElseIf Me.[S] = "02" And Me.Status = "Leased to other" And Me.PCT

0.75
Then
L1.BackColor = 255
Else: L2.BackColor = 16777215
End If

S (section), status and percent are all fields in the detail section
of
the
report. It didn't matter if I moved the map to the detail section -
it was
still inaccurate.
Should I simply set up a function in a query to create a new field
representing the section status? Also, there are 36 sections and I
would
really like to simplify the code - I assume there's an easy way with
an
array. Could you point me in the right direction on that, too.
Thanks

Let's try this ---

Make a copy of your report and let's make some changes. First, make the
recordsource property blank. Remove all textboxes that show data. Move your
map labels into the detail section. Make sure your labels are named L1 to
L36 and the captions are 1 to 36. Now I am going to assume you have a table
named TblLandSection that records the data for the 36 sections and the
fields are Section, Status and Percent. I am assuming all sections are
numbered from 1 to 36. Create a query based on TblLand Section named
QryLandSection and sort ascending on Section.

Put the following code in the report's Open event:
Dim Db As DAO.Database
Dim Rst As DAO.Recordset
Dim intS as Integer
Dim strS as String
Set DB = CurrentDB
Set Rst = DB.OpenRecordset("QryLandSection")
For intS = 1 to 36
If Rst!Status = "In-Hand" And Rst!PCT 0.75 Then
Me("L" & intS).BackColor = 16744576
ElseIf Rst!Status = "Leased to other" And Me.PCT 0.75
Me("L" & intS).BackColor = 255
Else
Me("L" & intS).BackColor = 16777215
End If
Next
Rst.Close
Set Rst = Nothing
Set DB = Nothing

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications





"okschlaps" wrote in message
...
Steve
Yeah, Duane was right, the code should have been for L2 in the second loop
(then L3 in the third, etc.) Each Label corresponds to a Section. I have
them
laid out in a grid (it all has to do with land work.)
I suspect Duane is right, that because my data are in the detail section
and
the "map" (labels) are in the group footer, it's not processing correctly.
I
tried to do a print screen of my report, but can't paste it in here.
I'll try to clarify. There are 36 sections. I need each section
represented
on a map, or grid and am using color to indicate the status of that
particular section according to the criteria specified in the code.
So the map looks something like
6 5 4 3 2 1
7 8 9 10 11 12
18 17 16 15 14 13
etc
Each number would be in a label and I'm trying to color these.
Hope that helps and I really appreciate your efforts!

"Steve" wrote:

Here is the second If loop in your original post --
If Me.[S] = "02" And Me.Status = "In-Hand" And Me.PCT 0.75 Then
L1.BackColor = 16744576
ElseIf Me.[S] = "02" And Me.Status = "Leased to other" And Me.PCT
0.75
Then
L1.BackColor = 255
Else: L2.BackColor = 16777215
End If

Note in the If and ElseIf statements you try to change the BackColor of
L1
but in the Else statement you try and change the BackColor of L2. I asked
you in my post about this. Duane's code differs from your code in that
his
code looks at the properties of Section 02 and for ALL conditions tries
to
change the BackColor of L2.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications






"okschlaps" wrote in message
...
I spoke too soon. The code runs, but only the last label is colored in.
For
instance, in one group, there are three sections that meet the
criteria,
but
only the last is colored in. Are all labels reformatted according to
the
last
"for" in the loop?

"Duane Hookom" wrote:

As Steve suggested, you might be able to change all your code to
something
like:
Dim intS as Integer
Dim strS as String
For intS = 1 to 36
strS = Format(intS,"00")
If Me.[S] = strS And Me.Status = "In-Hand" And Me.PCT 0.75 Then
Me("L" & intS).BackColor = 16744576
ElseIf Me.[S] = strS And Me.Status = "Leased to other" And Me.PCT

0.75
Me("L" & intS).BackColor = 255
Else
Me("L" & intS).BackColor = 16777215
End If
Next

It isn't clear what your records look like. We understand something
doesn't
work but we don't know much more.
--
Duane Hookom
Microsoft Access MVP


"Steve" wrote:

In your second If loop, shouldn't L1 be L2?

This could all be set up in code in a Do loop rather than a series
of
36 If
loops.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications




"okschlaps" wrote in message
...
I have a report I would like to convert into a kind of map. I'm
trying
to
do
this by creating labels that correspond to particular sections. If
acreage
in
a section meets certain criteria, the label would be colored
accordingly.
The
labels are arranged contiguously into a map.
However, it's not working. Following is a sample of the code I'm
using for
an event procedure under the format event for the group footer for
each
STR
(made up of 36 sections.) But all my "maps" look the same and do
not
reflect
any of the data.
Here's the code:

If Me.[S] = "01" And Me.Status = "In-Hand" And Me.PCT 0.75 Then
L1.BackColor = 16744576
ElseIf Me.[S] = "01" And Me.Status = "Leased to other" And
Me.PCT

0.75
Then
L1.BackColor = 255
Else: L1.BackColor = 16777215
End If

If Me.[S] = "02" And Me.Status = "In-Hand" And Me.PCT 0.75
Then
L1.BackColor = 16744576
ElseIf Me.[S] = "02" And Me.Status = "Leased to other" And
Me.PCT

0.75
Then
L1.BackColor = 255
Else: L2.BackColor = 16777215
End If

S (section), status and percent are all fields in the detail
section
of
the
report. It didn't matter if I moved the map to the detail
section -
it was
still inaccurate.
Should I simply set up a function in a query to create a new field
representing the section status? Also, there are 36 sections and I
would
really like to simplify the code - I assume there's an easy way
with
an
array. Could you point me in the right direction on that, too.
Thanks

Steve
It ran, but all the labels came out blue.

"Steve" wrote:

Let's try this ---

Make a copy of your report and let's make some changes. First, make the
recordsource property blank. Remove all textboxes that show data. Move your
map labels into the detail section. Make sure your labels are named L1 to
L36 and the captions are 1 to 36. Now I am going to assume you have a table
named TblLandSection that records the data for the 36 sections and the
fields are Section, Status and Percent. I am assuming all sections are
numbered from 1 to 36. Create a query based on TblLand Section named
QryLandSection and sort ascending on Section.

Put the following code in the report's Open event:
Dim Db As DAO.Database
Dim Rst As DAO.Recordset
Dim intS as Integer
Dim strS as String
Set DB = CurrentDB
Set Rst = DB.OpenRecordset("QryLandSection")
For intS = 1 to 36
If Rst!Status = "In-Hand" And Rst!PCT 0.75 Then
Me("L" & intS).BackColor = 16744576
ElseIf Rst!Status = "Leased to other" And Me.PCT 0.75
Me("L" & intS).BackColor = 255
Else
Me("L" & intS).BackColor = 16777215
End If
Next
Rst.Close
Set Rst = Nothing
Set DB = Nothing

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications





"okschlaps" wrote in message
...
Steve
Yeah, Duane was right, the code should have been for L2 in the second loop
(then L3 in the third, etc.) Each Label corresponds to a Section. I have
them
laid out in a grid (it all has to do with land work.)
I suspect Duane is right, that because my data are in the detail section
and
the "map" (labels) are in the group footer, it's not processing correctly.
I
tried to do a print screen of my report, but can't paste it in here.
I'll try to clarify. There are 36 sections. I need each section
represented
on a map, or grid and am using color to indicate the status of that
particular section according to the criteria specified in the code.
So the map looks something like
6 5 4 3 2 1
7 8 9 10 11 12
18 17 16 15 14 13
etc
Each number would be in a label and I'm trying to color these.
Hope that helps and I really appreciate your efforts!

"Steve" wrote:

Here is the second If loop in your original post --
If Me.[S] = "02" And Me.Status = "In-Hand" And Me.PCT 0.75 Then
L1.BackColor = 16744576
ElseIf Me.[S] = "02" And Me.Status = "Leased to other" And Me.PCT
0.75
Then
L1.BackColor = 255
Else: L2.BackColor = 16777215
End If

Note in the If and ElseIf statements you try to change the BackColor of
L1
but in the Else statement you try and change the BackColor of L2. I asked
you in my post about this. Duane's code differs from your code in that
his
code looks at the properties of Section 02 and for ALL conditions tries
to
change the BackColor of L2.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications






"okschlaps" wrote in message
...
I spoke too soon. The code runs, but only the last label is colored in.
For
instance, in one group, there are three sections that meet the
criteria,
but
only the last is colored in. Are all labels reformatted according to
the
last
"for" in the loop?

"Duane Hookom" wrote:

As Steve suggested, you might be able to change all your code to
something
like:
Dim intS as Integer
Dim strS as String
For intS = 1 to 36
strS = Format(intS,"00")
If Me.[S] = strS And Me.Status = "In-Hand" And Me.PCT 0.75 Then
Me("L" & intS).BackColor = 16744576
ElseIf Me.[S] = strS And Me.Status = "Leased to other" And Me.PCT

0.75
Me("L" & intS).BackColor = 255
Else
Me("L" & intS).BackColor = 16777215
End If
Next

It isn't clear what your records look like. We understand something
doesn't
work but we don't know much more.
--
Duane Hookom
Microsoft Access MVP


"Steve" wrote:

In your second If loop, shouldn't L1 be L2?

This could all be set up in code in a Do loop rather than a series
of
36 If
loops.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications




"okschlaps" wrote in message
...
I have a report I would like to convert into a kind of map. I'm
trying
to
do
this by creating labels that correspond to particular sections. If
acreage
in
a section meets certain criteria, the label would be colored
accordingly.
The
labels are arranged contiguously into a map.
However, it's not working. Following is a sample of the code I'm
using for
an event procedure under the format event for the group footer for
each
STR
(made up of 36 sections.) But all my "maps" look the same and do
not
reflect
any of the data.
Here's the code:

If Me.[S] = "01" And Me.Status = "In-Hand" And Me.PCT 0.75 Then
L1.BackColor = 16744576
ElseIf Me.[S] = "01" And Me.Status = "Leased to other" And
Me.PCT

0.75
Then
L1.BackColor = 255
Else: L1.BackColor = 16777215
End If

If Me.[S] = "02" And Me.Status = "In-Hand" And Me.PCT 0.75
Then
L1.BackColor = 16744576
ElseIf Me.[S] = "02" And Me.Status = "Leased to other" And
Me.PCT

0.75
Then
L1.BackColor = 255
Else: L2.BackColor = 16777215
End If

S (section), status and percent are all fields in the detail
section
of
the
report. It didn't matter if I moved the map to the detail
section -
it was
still inaccurate.
Should I simply set up a function in a query to create a new field
representing the section status? Also, there are 36 sections and I
would
really like to simplify the code - I assume there's an easy way
with
an
array. Could you point me in the right direction on that, too.
Thanks