LEARN MS-ACCESS TIPS AND TRICKS: Report

Showing posts with label Report. Show all posts

Date2Text and Text2Date Functions

a.p.r. pillaiOctober 29, 2019Calculation, Custom Functions, Functions, Report No comments

Introduction.

We already have some frequently used Report Footer formatting simple functions, like Report Page Number formatting function =PageNo([page],[pages]), output: Page: 1/20 –> Page: 20/20, Report Period Function =Period([StartDate], [EndDate]), output : Period: 15/09/2007 To 30/09/2007 and =Dated() Function, output: Dated: 15/09/2007 on Report Footer. Even though they are simple Report Header/Footer formatting functions, it saves report design time. Check the following Links, if you have not yet come across those Functions earlier:

Useful Report Functions.

Continued on Page 2/- an indicator Label on Report Page Footer, on multi-page reports.

Our new Function formats the Date Value in the following sample Text form:

Sunday, 27th October 2019.

The Date2Text() Function Code.

Public Function Date2Text(ByVal dt As Date) As String
Dim txt As String, num As Integer

num = Day(dt)

   Select Case num
       Case 1, 21, 31
          txt = "st "
       Case 2, 22
          txt = "nd "
       Case 3, 23
          txt = "rd "
       Case 4 To 20, 24 To 30
          txt = "th "
   End Select
   
   Date2Text = WeekdayName(Weekday(dt)) & "," & Day(dt) & txt & MonthName(Month(dt)) & " " & Year(dt)
   
End Function

Copy and paste the above Code into the Standard VBA Module, save the Code and compile it.

Let us try out the Code directly from Debug Window. Press Ctrl+G to display the Debug Window if it is not already visible on the VBA editing Window.

Sample Test Runs.

D = #27-10-2019#

? Date2Text(D)
Result: Sunday, 27th October 2019

D=Cdate("22/10/2019")

? Date2Text(D)
Result: Tuesday, 22nd October 2019

D=DateValue("11/10/2019")

? Date2Text(D)
Result: Friday, 11th October 2019

? Date2Text(Date)
Result: Thursday, 31st October 2019

Weekday 1 to 7 is Sunday to Saturday. This depends on your Computer's Regional Settings. If it is not correct in your case, then change it in the Regional settings on your Computer.

Scope of this Function.

The Date2Text() Function can be placed in TextBox on Report Header, use on Date-Field Query Column, or on the Main Form (Main Switchboard or Control Screen, etc.) as general info.

The Text2Date() Function.

The Date2Text() Function’s complementary Function Text2Date() VBA Code is given below.

Public Function Text2Date(ByVal txtDate As String) As Date
Dim S, dt As String
    
    S = Split(txtDate, " ")
    dt = Str(Val(S(1))) & "-" & S(2) & "-" & S(3)
    Text2Date = DateValue(dt)

End Function

The Date converted into Text form can be changed back into a valid date format with the Text2Date() Function. There is no validation check performed on the input value for errors and expected to pass the parameter value in the correct input format, the same as the Date2Text() Function output.

Example:

? Text2Date("Thursday, 31st October 2019")

Result: 31-10-2019

Caution: If the parameter value is entered manually, then there should not be more than one space between each segment of the date text.

Continued on Page 2 on Report

a.p.r. pillaiMay 18, 2011Labels, Report, Reports No comments

Introduction

Normally, MS-Access reports can span several pages, and it is often useful to display the current page number along with the total number of pages. To achieve this, a TextBox is added to the Page Footer section of the report. In the Control Source property of this TextBox, you can enter an expression like the following:

=”Page: “ & [page] & “ of “ & [pages]

Result: Page 1 of 15

="Page: " & [page] & " / " & [pages]

Result: Page: 1 / 15

Report Date is also added in the page footer area like ="Date: " & format(date(), ”dd/mm/yyyy”)

We often find ourselves repeatedly writing these expressions whenever a new report is designed. If you are a VBA enthusiast, you can automate this process by creating small, reusable User-Defined Functions (UDFs) in your application. By calling the function from a Text Box’s Control Source property, you can quickly insert the required information into your report. I have created two such functions for this purpose—if you’d like to take a look at them, click here.

This approach is especially efficient for developers aiming to standardize functionality across multiple Access applications, much like the concept discussed in your blog post on the MS-Access Reference Library.

Returning to the topic of the page indicator, we aim to display page continuity information in the Report’s Page Footer, but with a slight variation. A report may consist of a single page or multiple pages. When the report spans more than one page, the footer on the first page should display: “Continued on Page 2.”

On subsequent pages (page 2, page 3, etc.), the label should update accordingly—for example, “Continued on Page 3”, and so on—up to the second-to-last page. This label should not appear on the final page. If the report consists of only a single page, the label should be omitted entirely.

Using the first example at the beginning of this article, a single-page report will simply print as “Page 1 of 1.”

Try out the Page Footer Setting

To try our new page labels, open one of your Reports with a few pages.
Create a Text Box, wide enough to display the label, like Continued on Page 99, at the Page Footer of the Report.
Write the following expression in the Control Source Property of the Text Box.
=IIf([Pages]>1 And [Page]<[Pages],"Continued on Page " & [Page]+1,"")
Save the Report and open it in Print Preview.
Check the last page of the Report. This label should not appear there.
Try this out on a single-page Report.

NB: [page], [pages] are System Variables and they should be used in the expression without change. &hypen;

Earlier Post Link References:

Easy-Read Reports

a.p.r. pillaiMarch 24, 2011Reading, Report, Reports No comments

Introduction.

When computer reports, accounting ledger statements, or purchase invoices are printed with closely spaced lines, readability improves if alternate lines are shaded with a light background color. Traditionally, pre-printed stationery was used for this purpose. Line printers typically print six lines of data within a one-inch vertical space. To emphasize headlines, the double-strike method was used, where the hammer struck each character twice—this being the only enhanced printing style available in line printers.

Line-printer-based reports were designed on graph-paper-like sheets, with carefully written code to position headings, data lines, and summaries precisely on the pre-printed stationery.

An A4 sheet (8.5 × 11 inches) provides 66 print lines vertically. Of these, one inch at the top and half an inch at the bottom are reserved as margins, while the alternate data lines are shaded in green—similar to the sample image shown below.

Right-click to open Large Image in New Tab/Window.

A4-size paper has 80 print positions across when a 10-character-per-inch (pitch) font size is used. This can be increased to 96 characters if a 12-character-per-inch character size is used.

The above details provide a general background about computer stationery. Reports designed for line printers were created with these specifications in mind. When necessary, you can design reports for plain paper by setting the section height and the text box control height to one-sixth of an inch, matching the line spacing used by traditional line printers.

Light Shading of Alternate Report Data Lines

We will use a simple trick to print alternate lines with a shaded background, making the report easier to read. There is no need for pre-printed stationery, as the shading will be applied dynamically during printing.

You can design a Quick Report and add a few lines of VBA code in the report’s class module. If you already have a report with closely spaced detail lines, you may skip directly to step 3 below.

Import Products Table from MS-Access sample database Northwind.mdb
Use the Report Wizard to design a Tabular Report with the Products Table, like the sample image given below:

Right-click to open Large Image in New Tab/Window.
Open the Report in Design View.
Select all the controls on the Detail Section of the Report and drag them to the right to get enough space to draw a Text Box on the left side to display Serial Numbers on the Report Lines. Drag the heading lines and position them to the right.
Draw a Text Box on the left side and write the expression =1 in the Control Source Property. Change the Name Property Value to SRL.
Change the Running Sum Property Value to Over All.
Create a Label control at the Page Header above the Text Box and change the Caption to SRL (for Serial Number).
Select all the controls in the Detail Section together and display the Property Sheet (F4 or Alt+Enter).
Change the Top Property Value to 0. All the controls will be shifted and positioned at the top edge of the Detail Section.
Select the Rectangle Tool from the Toolbox and draw a rectangle around all the text boxes in the Detail Section (see the design view image above).
Display the Property Sheet of Rectangle (F4) and change the Name Property Value to Box1.
Select the Send-to-Back option from the Arrange Menu to position the rectangle behind the text box controls.
Reduce the Detail Section height so that there is no empty space below the TextBox controls.
Display the VBA Code Module of the Report (ALT+F11).

Copy and paste the following lines of VBA Code into the Code Module and save the Report:

The Report Module VBA Code.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim xSrl
xSrl = [SRL]
If xSrl / 2 = Int(xSrl / 2) Then
   [Box1].BackColor = &HCCC8C2
Else
   [Box1].BackColor = &HFFFFFF
End If
End Sub

Open the report in Print Preview. You will find the result as shown in the report image at the top of this page.

Percentage on Report Summary

a.p.r. pillaiFebruary 13, 2011Calculation, Report, Reports, Summary No comments

Introduction.

Earlier, we solved this problem using a Query in the blog post Percentage on Total Query. This time, let’s see how to achieve the same result on a Report. Our task is to display each detail line’s value as a percentage of the report’s summary total.

The approach is simple:

Create a Report that lists the detailed values and includes a report-level summary (such as the total).
Insert a Text Box in the Detail section.
In the Text Box’s Control Source, write an expression that divides the detail value by the Report Summary total.

This way, each record line will show its proportional contribution to the overall total directly on the Report.

Design a Sample Report to Try.

Import the Order Detail Table from Microsoft Access Sample Database: C:\Program Files\Microsoft Office\Office11\Sample\Northwind.mdb
Open a new Query in SQL View, without selecting a Table from the displayed list.

Copy and paste the following SQL String into the SQL editing window of the new Query:

SELECT [Order Details].[Order ID], Sum([Order Details].Quantity) AS TQuantity, Sum([Order Details].[Unit Price]) AS UnitPrice, Sum([Unit Price]*[Quantity]) AS TotalPrice
FROM [Order Details]
GROUP BY [Order Details].[Order ID];

Save the Query with the name OrderSummary.
Design a Report (as shown in the image given below) with the Detail Section and Report Footer summary controls using the OrderSummary as the Source.
Right-click to open Large Image in New Tab/Window.
Click on the Text Box at the Footer of the Report to select it.
Display the Property Sheet (F4 or ALT+Enter) of the Text Box.
Change the Name Property Value to GTPrice (stands for Grand Total Price).
Write the expression =Sum([TotalPrice]) in the Control Source Property.
Select the Text Box at the right end of the Detail Section and display its Property Sheet.
Write the expression =[TotalPrice]/[GTPrice] in the Control Source Property.
In the Format Property, select the Percent format from the drop-down list.
Type 2 in the Decimal Places Property.
Save and Close the Report.
Open the OrderSummary in Print Preview and check the detail line percentage value calculated on the Report Footer Grand Total Price.