Concatenating a text box with possible null values

[DMainland]

I am using a simple text box to return a Plaintiff vs. Defendant title
("Black vs. White") on a report. I would like to know how I can concatenate
the fields to
give me not only the Plaintiff vs. Defendant but would also return a Co
Plaintiff and a 2nd CoPlaintiff, and a Co Defendant and a 2nd Co Defendant or
any combination thereof, in the event I have a case including a combination
of multiple parties. I hope I am making myself clear.

The names of plaintiff, co-plaintiff, 2nd co-plaintiff, defendant,
co-defendant, and 2nd co-defendant are entered into separate tables joined by
a common docket number (autonumber).

Any help would be greatly appreciated.

 

[Tim Ferguson]

I am using a simple text box to return a Plaintiff vs. Defendant title
("Black vs. White") on a report. I would like to know how I can
concatenate the fields to
give me not only the Plaintiff vs. Defendant but would also return a
Co Plaintiff and a 2nd CoPlaintiff, and a Co Defendant and a 2nd Co
Defendant or any combination thereof, in the event I have a case
including a combination of multiple parties. I hope I am making
myself clear.

The names of plaintiff, co-plaintiff, 2nd co-plaintiff, defendant,
co-defendant, and 2nd co-defendant are entered into separate tables
joined by a common docket number (autonumber).

GUI method (easiest to implement and probably faster if you have large
number of rows): create a subreport getting names out of the Litigants
table with suitable ChildField and ParentField fields.

VBA method: create a procedure that reads a suitable query and iterate it
to create the string. You can call this in the OnFormat event (I think)
and insert the formatted text into the control. It needs to be highly
optimised unless it only has to run a few times. Try something like

Private Function GetLitigants( _
CaseNum as Long, _
IsDefendant as Boolean _
) As String


Dim output as String
DDim qdf as Querydef ' this is DAO, but you can
Dim rs as Recordset ' the similar thing in ADO

' open the query and provide its parameters
Set qdf = QueryDefs("GetLitigants")
With qdf
' details of the query obviously depend on your local
' table design etc etc. Don't forget to sort them
' suitably!
.Parameters("CaseNum") = CaseNum
.Parameters("Type") = Iif(IsDefendant,"Def","Plf")
' forward only snapshot is fastest and we need speed!
Set rs = .OpenRecordset(dbOpenSnapshot, dbForwardOnly)
End With

' read the names and concatenate them
Do While Not rs.EOF
' punctuation
If Len(output)>0 Then output = output & ", "
' read
output = output & rs!FullName
' don't want infinite loop!
rs.MoveNext
Loop

' all done, tidy up
rs.Close
qdf.close

GetLitigants = output

End Function


which is air code and completely untested, but you get the picture.

Hope that helps


Tim F

 

[David C. Holley]

Here's something to get you started. You'll have to adapt it to the
specific of you DB which I didn't have access to. (Evidently the court
order to turn over that information was misfiled.) I'm assuming that you
have some way of designating if a person is a plaintif or defendant. If
there are no plaintifs or defendants, the result will be [v. ],
otherwise the result should list out the formal title.

function getCaseTitle(strDocketNumber as String)

strSQL = "SELECT field1, field2, field3 FROM tblParties WHERE type =
'Defendant';"

Set db = currentDb()
'Get defendants
Set rs = CurrentDB.OpenRecordSet(strSQL, dbOpenForwardOnly)

getCaseTitle = ""

While NOT rs.EOF
If flgAddComma = True then
getCaseTitle = getCaseTitle & ", " & rs("fieldName")
else
getCaseTitle = getCaseTitle & rs("fieldName")
end If
flgAddComma = True
rs.MoveNext
Wend
rs.close

'Get plaintifs
strSQL = "SELECT field1, field2, field3 FROM tblParties WHERE type =
'Plaintif';"
Set rs = CurrentDB.OpenRecordSet(strSQL, dbOpenForwardOnly)

fglAddComma = False
getCaseTitle = getCaseTitle & " v. "

While NOT rs.EOF
If flgAddComma = True then
getCaseTitle = getCaseTitle & ", " & rs("fieldName")
else
getCaseTitle = getCaseTitle & rs("fieldName")
end If
flgAddComma = True
rs.MoveNext
Wend

rs.close
Set rs = Nothing
Set db = Nothing

end fucntion