Use Functions In Your Access Reports
Access reports have a number of built-in functions you can use to customize
your reports. Place a textbox in your report page footer and get rid of the
label if one is created for you. To get the current date and time that the
report was printed, type
=Now().
Whenever you see two reports of the same type, you can immediately see which
is the most current by checking the page footers.
Access also includes common expressions like Page X or Y and, for
workgroup security enabled databases (ones that you have to log into), it
can provide the Current User. With that information, you can see not only
when the report was created, but who ran it.
Of even greater use than the built-in functions are custom functions
written in VBA. These functions are written in the Visual Basic for
Applications language and stored in the section of the database called
Modules.
Even if you’re not a VBA programmer, you can create simple functions for
use in your Access reports. A function is created by going to Modules in the
Objects bar and clicking New.
You’ll find yourself in the Visual Basic Editor. Don’t freak out! You can
get back to Access at any time. Let’s try a simple function that returns our
current confidentiality statement (legal language is always changing!) Type
the text below (or copy and paste it) into the VB Editor:
Public Function ConfStat() As String
ConfStat = "I Get It! Development Confidential. You may not publish or
reproduce."
End Function
This creates a function called ConfStat. Not much of a program, but the
nice thing is that you can include a reference to this function in the Page
Footer of all of your confidential Access reports by typing
=ConfStat().
If (when) your confidentiality statement language changes you can update
the function in the VB Editor and all of your reports will update at the
same time.
You can find more samples of VBA code in our
Sample Code section.