Help on comma delimited


J

JOM

I have 15 fields in my table that are true and false. On my Form, a user can
only select upto 3 choices.

I would like to put only that information that the user selected true on the
report, e.g. If I selected Q1,Q3,Q10 I would like to view my report as
follows:
I would like to change the Q1-Q15 to something more understanding like Q1 =
nnn, Q3 =xyz Q10 = Abc Q6= etc
The information listed below is all in one table! (

Loan Name Address Reasons
0001 Smith John 123 Main Street nnn, xyz, Abc
0002 Mary Perkin 540 Hwy 35 N Etc

Danny Lesandrini helped by providing me with some code, but i am having some
errors with it

Function GetTrueResponses(ByVal lLoanID As Long) As String

Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim fld As DAO.Field
Dim sql As String
Dim strOut As String

Set dbs = Currentdb()
sql = "SELECT Q1, Q2, Q3, Q4, Q5, Q6, Q7, Q8, Q9, Q10 " & _
"FROM tblLoanTable WHERE LoanID = " & lLoanID
Set rst = dbs.OpenRecordset(sql,DbOpenSnapshot)

If Not rst.Bof and Not rst.Eof Then
For Each fld In rst.Fields
If fld = true then strOut = strOut & fld.Name & ", "
Next
Loop
If Len(strOut) <> 0 Then strOut = Left(strOut, Len(strOut)-2))
GetTrueResponses = strOut

Set rst = Nothihg
Set dbs = Nothing

End Function


Then, in your report query, you would add a field to the QBE like this ...

Reasons: GetTrueResponses([LoanID])
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top