Customizing string output for report based on field value

[RachelD]

I am producing a report on which space is a premium. I'm trying to take three fields, each containing an integer and build a string as follows:

If FieldA > 0, then the first part of the string is FieldA's contents plus the text " Added"
If FieldA = 0, then I don't want anything to appear in the string at all
Same thing with fields B and C, except that the additional text is different.
In the end, I want to see strings like "9 Added, 4 Retained, 10 Seasonal" or "6 Retained, 5 Seasonal" or just "4 Added" on my report.

I'm having some difficulty figuring out how best to do this. Past attempts typically end up mired in nested if statements or incorrect references to one of the fields.

Any help would be appreciated.
Thanks,
Rachel

 

[67peshawar294]

Rachel,
Try, in an unbound textbox on the report;

=IIf([fielda]>0 And [fieldb]>0 And [fieldc]>0,[fielda]& "," & "added" & [fieldb]& "," & "retained" & [fieldc] & "seasonal"," ")

However, if there are nulls in any of the fields the textbox will be blank
jim

 

[RachelD]

Thanks. I didn't think about putting the code itself in the unbound textbox. Question though - How does the And work? It looks like this means all fields have to be greater than 0, when in reality 0 to all 3 fields could have values greater than 0.

 

[67peshawar294]

Rachel,
You are right!
I neglected the case where a,b or c could be 0.
I will try to work it somemore.
jim

 

[RachelD]

Here's something I started working on. Problem here is I'm not sure how to dump the results of the code (the string value strEmp) into a field on the report. Right now this code is in the On Format event of the Detail portion of the report.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim strPart As String
Dim strEmp As String

If EmplAdded > 0 Then
strPart = EmplAdded & " Added"
strEmp = strPart
End If
If EmpRetained > 0 Then
strPart = EmpRetained & " Retained"
strEmp = strEmp & strPart & ", "
End If
If EmpSeasonal > 0 Then
strPart = EmpSeasonal & " Seasonal"
strEmp = strEmp & strPart
End If
End Sub

 

[67peshawar294]

You were definately on the right track.
I entered this in one of my db reports(with other fields) and it displayed the correct results, except the commas are printed when there is no data...
jim

 Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Dim strPart, strpart1, strpart2 As String
    Dim strEmp As String

 If Me.empadded > 0 Then
        strPart = Me.empadded & " Added"
        Text66 = strPart & ", " & strpart1 & ", " & strpart2
        End If
    If Me.empretained > 0 Then
        strpart1 = Me.empretained & " Retained"
        Text66 = strPart & ", " & strpart1 & ", " & strpart2 
       End If
    If Me.retained > 0 Then
        strpart2 = Me.retained & " Seasonal"
        Text66 = strPart & ", " & strpart1 & ", " & strpart2
        End If

 

[RachelD]

I made a few changes to the code to handle the commas. Also, I was having a problem where if all the fields were equal to 0, I was picking up the last integer from the previous record by default. So I inserted the "else". It probably isn't necessary for anything but the EmplAdded statement, but I thought I'd be consistent.

Last problem - to chop off the trailing comma, I put in a Left statement. Only thing is I get a runtime error 5 when running it.

Again, thanks for all your help. I work for a small company - nobody else to help work through this stuff.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim strPart, strpart1, strpart2 As String
Dim strEmp As String

If Me.EmplAdded > 0 Then
strPart = Me.EmplAdded & " Added, "
Text66 = strPart & strpart1 & strpart2
Else
strPart = ""
Text66 = strPart & strpart1 & strpart2
End If
If Me.EmpRetained > 0 Then
strpart1 = Me.EmpRetained & " Retained, "
Text66 = strPart & strpart1 & strpart2
Else
strpart1 = ""
Text66 = strPart & strpart1 & strpart2
End If
If Me.EmpSeasonal > 0 Then
strpart2 = Me.EmpSeasonal & " Seasonal, "
Text66 = strPart & strpart1 & strpart2
Else
strpart2 = ""
Text66 = strPart & strpart1 & strpart2
End If
Text66 = Left(Text66, Len(Text66) - 2)
End Sub

 

[67peshawar294]

Rachel,
Yeah! Looks good to me. I know what you mean, small company. I work for 3 ophthalmologists and have stumbled through Access since it first came out, Condor3 before that. No experience with VB. Ran accross tek-tips 2 years ago. What a blessing.
jim