Print dependent on Select Case value

[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

 

[Graham Mandeno]

Hi Al

I assume FieldName1, 2, 3 etc are fields in the RecordSource of your report.
In which case, the problem is that by saying "Dim FieldName1" you are
actually redeclaring FieldName1 to be a variable local to your function, and
completely unrelated to your actual field.

If you remove all the Dims you should just be able to set the ControlSource
of your dependent textbox to:
=PrintOutput([EventNo])

Note that your function should be in your report's class module and should
be declared as Private.

Also, Access optimises queries for reports by leaving out RecordSource
fields it considers unnecessary, so any field you refer to in your function
must be bound to a control on your report (even if that control is an
invisible textbox).

Finally, the "Is =" is unnecessary in a Case statement. It is usual just to
use:
Case 1
Case 2
etc

 

[AlTamres]

Thanks Graham
I removed the Dim statements and the is = and declared the function as
private
I placed a hidden text box for each field
Still having problems will not work. I get compiler errors and cannot print
the report.
Any additional suggestions?
Thanks again Al

Hi Al

I assume FieldName1, 2, 3 etc are fields in the RecordSource of your
report. In which case, the problem is that by saying "Dim FieldName1" you
are actually redeclaring FieldName1 to be a variable local to your
function, and completely unrelated to your actual field.

If you remove all the Dims you should just be able to set the
ControlSource of your dependent textbox to:
=PrintOutput([EventNo])

Note that your function should be in your report's class module and should
be declared as Private.

Also, Access optimises queries for reports by leaving out RecordSource
fields it considers unnecessary, so any field you refer to in your
function must be bound to a control on your report (even if that control
is an invisible textbox).

Finally, the "Is =" is unnecessary in a Case statement. It is usual just
to use:
Case 1
Case 2
etc

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

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

 

[Graham Mandeno]

Hi Al

It would be a whole lot easier to give you an answer if you (a) posted your
code and (b) told us which line is throwing the error and what the error is!

--


Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks Graham
I removed the Dim statements and the is = and declared the function as
private
I placed a hidden text box for each field
Still having problems will not work. I get compiler errors and cannot
print the report.
Any additional suggestions?
Thanks again Al

Hi Al

I assume FieldName1, 2, 3 etc are fields in the RecordSource of your
report. In which case, the problem is that by saying "Dim FieldName1" you
are actually redeclaring FieldName1 to be a variable local to your
function, and completely unrelated to your actual field.

If you remove all the Dims you should just be able to set the
ControlSource of your dependent textbox to:
=PrintOutput([EventNo])

Note that your function should be in your report's class module and
should be declared as Private.

Also, Access optimises queries for reports by leaving out RecordSource
fields it considers unnecessary, so any field you refer to in your
function must be bound to a control on your report (even if that control
is an invisible textbox).

Finally, the "Is =" is unnecessary in a Case statement. It is usual just
to use:
Case 1
Case 2
etc

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

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