CODEWITHSUNDEEP
ms-access
Kyle
Kyle

Reputation: 250

Print Access Report formulas

Does anyone know if there is a way to print formulas on a MS Access report, much like the way you can change settings in Excel to show only the formulas and no data in a worksheet?

I could possibly go through and wrap all formulas in quotes, but I'm looking to see if there is a setting in the program that will do this for me.

Upvotes: 0

Views: 502

Answers (2)

Kyle
Kyle

Reputation: 250

In the time that has lapsed since I created this post, I found a great trick for viewing the formulas in a report. Using the Database documenter on a report, you can create a Word file that is searchable. Example: If you want to find and replace several field names in formulas across your report, you can use the Database Documenter to document the report, and then search in Word for all instances of the field names you are trying to replace. The search will show you all of the controls in which the field names (or any string of text, for that matter) exist. Then, you can go back to Access, pull those controls up via the dropdown at the top of the properties panel, and change the expressions associated with those controls.

Upvotes: 1

Praesagus
Praesagus

Reputation: 2094

Sorry, there is none. You are stuck wrapping them in quotes. :( Using VB might save you some time - try something like this.

Function formula()
    On Error GoTo OutOfSections
    Const sRpt As String = "Report1"
    Dim rpt As Access.Report
    Dim ctl As Control
    Dim iCounter As Integer
    DoCmd.OpenReport sRpt, acViewDesign
    Set rpt = Reports(sRpt)
    Do
        For Each ctl In rpt.Section(iCounter).Controls
            If ctl.ControlType = acTextBox Then
                Debug.Print "=""" & Replace(ctl.ControlSource, """", "'") & """"
                ctl.ControlSource = "=""" & Replace(ctl.ControlSource, """", "'") & """"
            End If
        Next
        iCounter = iCounter + 1
    Loop
OutOfSections:

End Function

Upvotes: 1

Related Questions