A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Expression in unbound control



 
 
Thread Tools Display Modes
  #1  
Old August 17th, 2008, 05:27 PM posted to microsoft.public.access.reports
Mike from Moriches
external usenet poster
 
Posts: 11
Default Expression in unbound control

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

  #2  
Old August 17th, 2008, 06:19 PM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default Expression in unbound control

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")

--
Duane Hookom
Microsoft Access MVP


"Mike from Moriches" wrote:

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


  #3  
Old August 17th, 2008, 06:34 PM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Expression in unbound control

Mike from Moriches wrote:

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.

--
Marsh
MVP [MS Access]
  #4  
Old August 18th, 2008, 01:03 PM posted to microsoft.public.access.reports
Mike from Moriches
external usenet poster
 
Posts: 11
Default Expression in unbound control

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

"Marshall Barton" wrote in message
...
Mike from Moriches wrote:

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.

--
Marsh
MVP [MS Access]


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:32 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.