CODEWITHSUNDEEP
ms-accessformat
Misen
Misen

Reputation: 3

Set field format in query to report

Is there a way for a report's field to take into account the format of a field in a query?

In example:

I have a StudentPercent field in a query. Values of the field are between 0 to 1, but since it is formatted to percent, they appear from 0% to 100% . When I run the report, it doesn't consider the format of the field and the values are between 0 to 1. Why is that?

Edit 1: I'm using Microsoft Access 2016. Also, datas are populated dynamically, so I can't just set the format of the fields manually.

Edit 2:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
'Exit Sub
'  Place values in text boxes and hide unused text boxes.

    Dim intX As Integer
    '  Verify that not at end of recordset.
    If Not rstReport.EOF Then
        '  If FormatCount is 1, place values from recordset into text boxes
        '  in detail section.
        If Me.FormatCount = 1 Then
            Me("Col" + Format(intColumnCount + 1)) = 0
            For intX = 1 To intColumnCount
                '  Convert Null values to 0.
                Me("Col" + Format(intX)) = Nz(rstReport(intX - 1))
                If intX < intColumnCount Then
                    Me("Col" + Format(intColumnCount + 1)) = _
                    Me("Col" + Format(intColumnCount + 1)) + Nz(rstReport(intX))
                End If
            Next intX

            '  Hide unused text boxes in detail section.
            'For intX = intColumnCount + 2 To conTotalColumns
                'Me("Col" + Format(intX)).Visible = False
            'Next intX
        For intX = 2 To intColumnCount + 1
            Me("Tot" + Format(intX)) = Nz(Me("Tot" + Format(intX))) + Nz(Me("Col" + Format(intX)))
        Next intX

            '  Move to next record in recordset.

            rstReport.MoveNext
        End If
    End If
End Sub

^ is the code of the detail part of my report. I'm getting the error '13' - Type mismatch when I run the report after casting my field with Format(FieldName, "Percent") and the following code is highlighted:

                Me("Col" + Format(intColumnCount + 1)) = _
                Me("Col" + Format(intColumnCount + 1)) + Nz(rstReport(intX))

Upvotes: 0

Views: 46

Answers (1)

Gustav
Gustav

Reputation: 55831

Set the Format property of the textbox in the report to: Percent

Or, expand the source query to have a field returning the formatted value as text:

StudentPercentText: Format([StudentPercent],"Percent")

Then use this field in your report and not the StudentPercent field. However, this is text, so you cannot use such a field in a calculation in the report.

Upvotes: 2

Related Questions