Text manipulation in Access Report

[BethHill]

Hello,

I have the following code, which produces multiple columns in the
group_footer section. (The detail section is not visible.)

Do Until rs.EOF 'Do this procedure until the End of File is reached.
intLength = 0
stResults = ""
Do Until Len(stResults) >= 85
'rs.MoveFirst
intLength = Len(Requirement)
Do While intLength <= 35
stResults = stResults & Requirement & " "
intLength = Len(stResults)
'rs.MoveNext
Loop
text0.Value = stResults
'intLength = 0
rs.MoveNext

If rs.EOF Then Exit Do
Loop 'The text box is full
Loop 'End of File

The output is 6 columns, which is what I want, but repeats the last record
in the recordset instead of each record. Example:

ABC123 ABC123 ABC123 ABC123 ABC123......

I want it to appear like the following:

ABC123 DEF456 GHI789 JKL101 MNO123......

What am I doing wrong?

 

[Marshall Barton]

I have the following code, which produces multiple columns in the
group_footer section. (The detail section is not visible.)

Do Until rs.EOF 'Do this procedure until the End of File is reached.
intLength = 0
stResults = ""
Do Until Len(stResults) >= 85
'rs.MoveFirst
intLength = Len(Requirement)
Do While intLength <= 35
stResults = stResults & Requirement & " "
intLength = Len(stResults)
'rs.MoveNext
Loop
text0.Value = stResults
'intLength = 0
rs.MoveNext

If rs.EOF Then Exit Do
Loop 'The text box is full
Loop 'End of File

The output is 6 columns, which is what I want, but repeats the last record
in the recordset instead of each record. Example:

ABC123 ABC123 ABC123 ABC123 ABC123......

I want it to appear like the following:

ABC123 DEF456 GHI789 JKL101 MNO123......

Looks like there are lots of things wrong in there. First,
you are using a variable (or control) reference to
Requirement instead of a field in the recordset. This will
alway retrieve the same value so that explains that part.
I'm guessing that Requirement is also a field in the
recordset, in which case you need to use:
strResult = strResult & rs!Requirement

Second, you are not incrementing the length of the current
line so it will be difficult to determine when another line
is starting.

Your use of loops within loops with nothing that controls
the loop changing what it does. I'm pretty sure that you
should get rid of those inner loops and use an If statement
to add vbCrLf after the length of the string reaches the
limit of 85(?) characters:
If intLength >= 85 Then
strResults = strResults & vbCrLf
intLength = intLength - 85
End If
intLength = intLength + Len(rs!Requirement)

I have no idea what the check fo 35 is supposed to
accomplish, but it kind of looks like it's redundant.

None of what I posted above is supposed to work as is, it is
just a collection of topics that you need to think about
before rewriting the code into a more coherent procedure.