Report using Select Case

[Al Hotmail]

Print a report with a field value that is dependent on value of another
field
I have a report that is based on a query
The output is grouped into 23 different events
Under each event the record name field is printed
To the right of the name I need to print the value of a field which is
dependent on the event value
Some of the fields are date, strings, and integer
I am having trouble getting a module using Select Case function to work
Some events require 4 lines of information to be printed from 4 different
fields.

Function PrintOutput (EventNo As Long) As String
Dim FieldName1 as Date
Dim FieldName2 as Date
Dim FieldName3 as Date
Dim FieldName4 As String
Dim Field_Name5 As String
Dim Field_Name6 As integer
Dim FieldName7 As integer
' continued to field name 23

Select Case EventNo
Case Is = 1
PrintOutput = FieldName5
Case Is = 2
PrintOutput = Format(FieldName2, "mmm. d, yyyy")
Case Is = 3
PrintOutput = FieldName4 & vbCrLf &_
FieldName5 & vbCrLf &_
FieldName7 & vbCrLf &_
FieldName20
Case Is = 4
PrintOutput = Format(FieldName2, "mmm. d, yyyy")
Case Is = 5
PrintOutput = FieldName4
Case Is = 14
PrintOutput = If Field6 = -1 Then Field9
Else
"??UNKNOWN??"
End If
End Function

Any help will be greatly appreciated
Thanks in advance
Al

 

[Duane Hookom]

You would need to have an unbound text box to set to a field or fields. All
fields would need to be bound to controls in your report but could be hidden.

I'm not sure why you have Dim'd FieldName... since these should probably
reference bound controls in your report.

Your request is a bit unusual. I would consider creating a public function
with a ton of arguments to get the code out of your report.

Public Function GetPrintOut(lngEvenID as Long, str1 as String, str2 as
String, ....) as String
'your Select Case with End Select here

End Function

You could then use the function in the query or in a control source in the
report.

 

[AlTamres]

Thanks Duane
I am still having problems.
I don’t know the need or use of unbound text box. is this the same item as
the fields bound to a control. I think it is the same.
I created a public function with all fields called out and identified.
In the field that I wanted to printed I typed =printout( EventNo as long,
etc as indicated above)
I receive compiler error.
I changed field names to remove spaces.
Any other suggestions?
Thanks again Al

 

[Duane Hookom]

I am having trouble understanding what you are attempting to accomplish and
why. This is very unconventional and as i stated, your code doesn't make
sense. I don't know what is bound or unbound in your report.

Where is the function located and where is it used?

 

[Al Hotmail]

I have a query that combines all the data, it has a field “CodeNo†that
indicates what field (event) value was changed and needs to be reported.
The report is grouped by the field (Event) that was changed. When I print
the report I title the section with the changed event name as an example
Address, Date of Death, Date received a new position, etc.. I then print the
person’s name, and IDNo. and to the right of the name and IDNo I want to
print the changed or value of a field that is determined by the “CodeNoâ€(Event).

I hope this clarify what I am trying to do. If you want to talk my number is
410-655-7580 however I will not be available from 8/8/09 until 8/17/09
Thanks again Al

 

[Duane Hookom]

I would probably bind all of the possible fields to invisible text boxes that
are in the section of the report. Set each of their tag properties to
correspond with EventNo. The code below assumes you have a text box named
txtEventNo that is bound to the EventNo field. Then use in the On Format
event of the section like:

Dim ctl As Control
For Each ctl in me.controls
If Len(ctl.Tag) > 0 Then
ctl.Visible = ctl.Tag = Me.txtEventNo
End If
Next

The invisible controls can be stacked and have a height of 1 with Can Grow
set to Yes.