Generate value for text field in report based on query

[ahuvas]

Okay I have a report (rptHorneOstbergQuestionnaire) that is based on a query (qryrptHorneOstbergQuestionnaire). In the report I have a total (HOTotal) which is the result of an expression created in the qry. Based on this result I would like to generate text in a text field (HOType) that is found in the same report.

Basically if the field HOTOtal shows any vaue between:
16 and 30 then I want the unbound text field to show the text Definitely evening typw and so on ...(see case statement below)

I thought I could do a case statement on report open but I am getting an error that my expression contains no value.

Here is the case statement

Private Sub Report_Open(Cancel As Integer)

Select Case Me.HOTotal

Case 16 To 30
Me.HOType.Value = "Definitely evening type"
Case 31 To 41
Me.HOType.Value = "Moderately evening type"
Case 42 To 58
Me.HOType.Value = "Neither type"
Case 59 To 69
Me.HOType.Value = "Moderately morning type"
Case Else
Me.HOType.Value = "Definitely morning type"

End Select

End Sub


What else can I do?

 

[stopher]

The On Open event is the wrong place to do this kind of this imo. The On Open event doesn't really know about the values of individual records.

What you need to do is create your own Public Function in a module.

Your function would look something like this:

Public Function HOType(Score As Variant)

If IsNull(Score) Then
    HOType = "Unassigned"
Else
    Select Case Score
    Case 16 To 30
        HOType = "Definitely evening type"
    Case 31 To 41
        HOType = "Moderately evening type"
    Case 42 To 58
        HOType = "Neither type"
    Case 59 To 69
        HOType = "Moderately morning type"
    Case Else
        HOType = "Definitely morning type"
    End Select
End If
End Function

Then you can just call the function in the control source of the Hotype field on your report. The control source will look like this:

=HOType(HOtotal)

If Hototal is summed in the report then the control source will look like this:

=HOType(sum(HOtotal))

Note that in both cases I'm referencing the underlying data field not the name of the control on the report.

hth
CHris

 

[ahuvas]

Yay it worked

Because Im such a relatively newbie I generally google the term i.e. generate text field query and see how other people are approaching similar problems. In this case I saw people doing iif statements, case statements etc. I know how to write the code but not always how to apply it. Thanks