DataReport

Hi,

I am trying to print a report on all the answer compiled in my database.

While Not RS.EOF
DataReport.Sections("Section1").Controls.Item("Answer").DataField = RS("Answer").Name
RS.MoveNext
Wend

However, the value stored in "Answer" is either 0 or 1, representing No or Yes.

How do I get datareport to print Yes or No instead of 0 or 1?

Thanx in advance.

Regards.

KT

You can set the "Answer" control to boolean. the bolean values act like.
[code]
-1 = True
0 = False
>= 1 = True
[code]
I hope i am right.. ;)

....but still only provides True and False, not Yes and No.

The only thing I can think of is setting a variable and using a SELECT CASE

i.e.

Code:

---

Dim sBool as Single
Dim strBool as String

Select Case sBool
Case 0
strBool = "No"
Case Else
strBool = "Yes"
End Select

---

really basic, but I'll work!

Gaffer

In your SQL you could try this:

SELECT AnswerYN = CASE Answer WHEN 0 THEN "No" ELSE "YES" END, AnotherField, AnotherField2 FROM ...

But you can also set the values from the propertie "DataFormat" = "Boolean"
where True = Yes and False = No and NULL value = Nothing. ;)

Thank you guys, for all the proposed solution.

Let me extend my query a little here.

I would like to print the Name of Student, Question, Answer. However, this time, the Answer is more than just Yes or No.

Answer Value Represents
--------------------------------
0 No
1 Yes
2 Don't Know
3 All


While Not RS.EOF
DataReport.Sections("Section1").Controls.Item("Question").DataField = RS("Question").Name
DataReport.Sections("Section1").Controls.Item("StudentName").DataField = RS("StudentName").Name
DataReport.Sections("Section1").Controls.Item("Answer").DataField = RS("Answer").Name
RS.MoveNext
Wend


For your kind advice, please.

Regards.

KT

Nathan,

I tried using your method but I got a "Method 'Open' of object_Recordset failed".

Dim QueryString
QueryString = "Select Question, StudentName, AnswerValue = CASE Answer WHEN 0 Then 'No' Else 'Yes' END From Result"
Set RS = New ADODB.RecordSet

RS.Open QueryString, cnn, adOpenDynamic, adLockOptimistic

While Not RS.EOF
DataReport.Sections("Section1").Controls.Item("Question").DataField = RS("Question").Name
DataReport.Sections("Section1").Controls.Item("StudentName").DataField = RS("StudentName").Name
DataReport.Sections("Section1").Controls.Item("AnswerValue").DataField = RS("AnswerValue").Name
RS.MoveNext
Wend

Please advise.

Thanx.

Regards.

KT

I think that if you are going to extend the number of choices from a simple Yes/No to 4 (and possibly more), then you may want to consider doing the mapping whilst looping through the recordset, and not in the query. Doing this by SQL is goint ot take a nested CASE…WHEN…END, which could affect performance on the server (depending on how many records you have, and how many nestings you need to do.

I feel that if the recordset is created locally, and by using a SELECT CASE, you will speed up the operation, and it willl be easier to maintain.

Thus:

Code:

---

While Not RS.EOF
DataReport.Sections("Section1").Controls.Item("Question").DataField = RS("Question").Name
DataReport.Sections("Section1").Controls.Item("StudentName").DataField = RS("StudentName").Name
Select Case rs(“AnswerValue”)
Case 0
        StrAnswer = “Yes”
Case 1 etc etc etc
End Select

DataReport.Sections("Section1").Controls.Item("AnswerValue").DataField = strAnswer
RS.MoveNext
Wend

---

Anyone still want to persevere with SQL for this?

Thanx Gaffer, but it doesnt seems to work.

The error is DataField "Yes" not found.

I believe Datafield is the name of the fieldname in the table instead of the value. Thus, passing amended value is not viable here.

Thanx tho.

Regards.

Kent Tan

What database are you using? I'm wondering if it's not supported by every database, or every database provider. I've been using SQL Server and the Microsoft OLE DB Provider for SQL Server. I haven't actually tried a case on more databases and providers so I'm not sure.

Also something else that I haven't tried:
Case field WHEN 0 Then 'Yes' WHEN 1 Then 'No' Else 'Maybe' End

Not sure if this will work or not but you could give it a try.

Anyway I'll work on it a little bit today if I get a chance.

Good luck.

Nathan,

I tried using your statement on SQL 7.0 and it works, however, not on Ms Access 2000.

Regards.

KT