Expression in unbound control

[Mike from Moriches]

I have a report listing records containing a [CommitteeStatus] field. The
field contains either a 1,2 or 3. I would like to display Pending, Approved
or Denied in an unbound control named Status instead of the digits. Nothing
prints in the control unless I put something in the datasource property, but
that won't take the If statements in the code below. If what I am
attempting is possible, please advise on the proper procedure. Thanks,
Mike from Moriches

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim strCstatus As String
strCstatus = "Pending"
If [CommitteeStatus] = 2 Then
strCstatus = "Approved"
End If
If [CommitteeStatus] = 3 Then
strCstatus = "Denied"
End If
Me!Status = strCstatus
End Sub

 

[Duane Hookom]

Ideally, you should have a small table with the number and the status. This
would allow you to join the small table in your report's record source.

If you don't want to create a table, you can set a control source to:
=Choose([CommitteeStatus],"Pending","Approved","Denied")

 

[Marshall Barton]

I have a report listing records containing a [CommitteeStatus] field. The
field contains either a 1,2 or 3. I would like to display Pending, Approved
or Denied in an unbound control named Status instead of the digits. Nothing
prints in the control unless I put something in the datasource property, but
that won't take the If statements in the code below.

You can set the text box's control source expression to:

=Choose(CommitteeStatus, "Pending", "Approved", "Denied")

However, it would be better to create a little table with a
status number field and another field for the corresponding
text. Then the report's record source query can join to
this table and include the text without using an expression.
The reason this approach is much better is that it's a lot
easier to modify the list of statuses without having to
change your program. E.g. Think about some manager
deciding that "Denied" is not adequate and tells you to
change it to "Filed for future consideration" and add two
new status values for "Stupid idea" and "Resubmit after
expanding idea".

This new table would also be used by a combo box in the data
entry form. so users can not make a mistake entering a
status.

 

[Mike from Moriches]

Thanks Duane and Marshall - The Choose function works just fine, and I like
the thought of "Planning ahead using the Table approach.

I have a report listing records containing a [CommitteeStatus] field. The
field contains either a 1,2 or 3. I would like to display Pending,
Approved
or Denied in an unbound control named Status instead of the digits.
Nothing
prints in the control unless I put something in the datasource property,
but
that won't take the If statements in the code below.

You can set the text box's control source expression to:

=Choose(CommitteeStatus, "Pending", "Approved", "Denied")

However, it would be better to create a little table with a
status number field and another field for the corresponding
text. Then the report's record source query can join to
this table and include the text without using an expression.
The reason this approach is much better is that it's a lot
easier to modify the list of statuses without having to
change your program. E.g. Think about some manager
deciding that "Denied" is not adequate and tells you to
change it to "Filed for future consideration" and add two
new status values for "Stupid idea" and "Resubmit after
expanding idea".

This new table would also be used by a combo box in the data
entry form. so users can not make a mistake entering a
status.