Formatting Question

[Stan]

I'm need to change the results of an Option Box in a report. In other words,
the option box returns 1, 2, or 3 and I would like it to say "Red", "Yellow",
or "Green" in the report. Is the code done in the 'Detail' section? Also,
I've tried a basic If Then statement but it doesn't like it. Any help would
be greatly appreciated!!

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

If QueueStatus = 1 Then
QueueStatus = "Green"
ElseIf QueueStatus = 2 Then
QueueStatus = "Yellow"

etc.

End Sub

 

[fredg]

I'm need to change the results of an Option Box in a report. In other words,
the option box returns 1, 2, or 3 and I would like it to say "Red", "Yellow",
or "Green" in the report. Is the code done in the 'Detail' section? Also,
I've tried a basic If Then statement but it doesn't like it. Any help would
be greatly appreciated!!

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

If QueueStatus = 1 Then
QueueStatus = "Green"
ElseIf QueueStatus = 2 Then
QueueStatus = "Yellow"

etc.

End Sub

That's not how option groups work.

Assuming 1 = Green, 2 = Yellow and 3 = Red .....

Include the [OptonStatus] field in the report. You can make it not
visible.
Then, using an unbound control, set it's control source to:
=Choose([OptionStatus],"Green","Yellow","Red")

Read VBA help on the Choose function.

That's the easy way.

If you do wish to use the If..Then.. construct in the Detail Format
event instead, then use an unbound text control. Leave it's control
source blank.
Code the Detail Format event:
If [OptionStatus] = 1 Then
Me.[ControlName] = "Green"
ElseIf Me.[OptionStatus] = 2 Then
Me.[ControlName] = "Yellow"
Else
Me.[ControlName] = "Red"
End If

 

[Stan]

Many apologies as I should have been more specific. The option box is on a
form that feeds a table. The report is generated based upon the results in
the table.

I'm need to change the results of an Option Box in a report. In other words,
the option box returns 1, 2, or 3 and I would like it to say "Red", "Yellow",
or "Green" in the report. Is the code done in the 'Detail' section? Also,
I've tried a basic If Then statement but it doesn't like it. Any help would
be greatly appreciated!!

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

If QueueStatus = 1 Then
QueueStatus = "Green"
ElseIf QueueStatus = 2 Then
QueueStatus = "Yellow"

etc.

End Sub

That's not how option groups work.

Assuming 1 = Green, 2 = Yellow and 3 = Red .....

Include the [OptonStatus] field in the report. You can make it not
visible.
Then, using an unbound control, set it's control source to:
=Choose([OptionStatus],"Green","Yellow","Red")

Read VBA help on the Choose function.

That's the easy way.

If you do wish to use the If..Then.. construct in the Detail Format
event instead, then use an unbound text control. Leave it's control
source blank.
Code the Detail Format event:
If [OptionStatus] = 1 Then
Me.[ControlName] = "Green"
ElseIf Me.[OptionStatus] = 2 Then
Me.[ControlName] = "Yellow"
Else
Me.[ControlName] = "Red"
End If

 

[John W. Vinson]

I'm need to change the results of an Option Box in a report. In other words,
the option box returns 1, 2, or 3 and I would like it to say "Red", "Yellow",
or "Green" in the report. Is the code done in the 'Detail' section? Also,
I've tried a basic If Then statement but it doesn't like it. Any help would
be greatly appreciated!!

No code is needed at all. Set the Control Source of the report control to:

=Choose([fieldname], "Red", "Yellow", "Green")

The Choose function takes an integer number as its first argument, and treats
its remaining arguments as an array, returning the nth element of the array if
the first argument is equal to n.

John W. Vinson [MVP]

 

[fredg]

Many apologies as I should have been more specific. The option box is on a
form that feeds a table. The report is generated based upon the results in
the table.

I'm need to change the results of an Option Box in a report. In other words,
the option box returns 1, 2, or 3 and I would like it to say "Red", "Yellow",
or "Green" in the report. Is the code done in the 'Detail' section? Also,
I've tried a basic If Then statement but it doesn't like it. Any help would
be greatly appreciated!!

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

If QueueStatus = 1 Then
QueueStatus = "Green"
ElseIf QueueStatus = 2 Then
QueueStatus = "Yellow"

etc.

End Sub

That's not how option groups work.

Assuming 1 = Green, 2 = Yellow and 3 = Red .....

Include the [OptonStatus] field in the report. You can make it not
visible.
Then, using an unbound control, set it's control source to:
=Choose([OptionStatus],"Green","Yellow","Red")

Read VBA help on the Choose function.

That's the easy way.

If you do wish to use the If..Then.. construct in the Detail Format
event instead, then use an unbound text control. Leave it's control
source blank.
Code the Detail Format event:
If [OptionStatus] = 1 Then
Me.[ControlName] = "Green"
ElseIf Me.[OptionStatus] = 2 Then
Me.[ControlName] = "Yellow"
Else
Me.[ControlName] = "Red"
End If

I understand that. The answer I gave you is correct. The name of the
field in the table, used in the report, is [OptionStatus]. If it is
actually named something else, simply change the name in the report to
match what is in the report's record source. I went with
[OptionStatus] because that's what you used.