REport Question

[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:
The information listed below is all in one table!

Loan Name Address Reasons
0001 Smith John 123 Main Street Q1,Q3,Q10
0002 Mary Perkin 540 Hwy 35 N Q6

 

[Danny J. Lesandrini]

Ahhh, the comma delimited list of choices. It woulf be great if this could be done
from query sql, but I don't believe there is any way, short of calling a custom function
that returns the comma delimited list. It might look something like this ...


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])


This will be slow, but it will give you the results you show below.

 

[JOM]

thanks for the reply, I will try it in just a few minutes, but I just wanted
to ask you something in reference to what you said. Why will this be slow.
Can I have my reports record source from a query and will this be a little
bit more faster??

Ahhh, the comma delimited list of choices. It woulf be great if this could be done
from query sql, but I don't believe there is any way, short of calling a custom function
that returns the comma delimited list. It might look something like this ...


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])


This will be slow, but it will give you the results you show below.
--

Danny J. Lesandrini
(e-mail address removed)
http://amazecreations.com/datafast

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:
The information listed below is all in one table!

Loan Name Address Reasons
0001 Smith John 123 Main Street Q1,Q3,Q10
0002 Mary Perkin 540 Hwy 35 N Q6

 

[JOM]

Danny, I am having an error in the Loop line...
Where do i put the Do statement

Ahhh, the comma delimited list of choices. It woulf be great if this could be done
from query sql, but I don't believe there is any way, short of calling a custom function
that returns the comma delimited list. It might look something like this ...


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])


This will be slow, but it will give you the results you show below.
--

Danny J. Lesandrini
(e-mail address removed)
http://amazecreations.com/datafast

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:
The information listed below is all in one table!

Loan Name Address Reasons
0001 Smith John 123 Main Street Q1,Q3,Q10
0002 Mary Perkin 540 Hwy 35 N Q6