Date2Text and Text2Date Functions

Date2Text and Text2Date Functions.

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, they save time on report design.  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 the 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 Code above into the Standard VBA Module, save the code, and compile it.

Let us try out the Code directly from the 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 a TextBox on the Report Header, used on a 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 to Text form can be changed back to a valid date format with the Text2Date() Function. There is no validation check performed, and it expects the parameter value to be in the correct input format, similar to the Date2Text() Function.

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.

1. Days in Month Function
2. Custom Calculator and Eval Function
3. Rounding Function MRound() of Excel
4. Proper Function of Excel

Read More

Continued on Page 2 on Report

Continued on Page 2 of the report.

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 Report Page Footer section. 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

OR

="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, 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 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

1. To try our new page labels, open one of your Reports with a few pages.

2. Create a Text Box wide enough to display the label Continued on Page 99, at the Page Footer of the Report.

3. Write the following expression in the Control Source Property of the Text Box.

4. =IIf([Pages]>1 And [Page]<[Pages],"Continued on Page " & [Page]+1,"")

5. Save the Report and open it in Print Preview.

6. Check the last page of the Report.  This label should not appear there.

7. Try this out on a single-page Report.

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

Earlier Post Link References:

• Join Split Array Function-2
• Join Split Array Function
• Memo Field and Data Filtering
• Lively Controls on Form
• Create your own Color Palette

Read More

Easy-Read Reports

Easy-Read Reports.

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 each character for enhanced printing style,  the only advanced feature 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. 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—see the sample image 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 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.

1. Import Products Table from MS-Access sample database Northwind.mdb

2. 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.

3. Open the Report in Design View.

4. Select all the controls on the Detail Section of the Report and drag them to the right to get enough space to draw a TextBox on the left side to display Serial Numbers on the Report Lines.  Drag the heading lines and position them to the right.

5. Insert a TextBox on the left side and write the '=1' expression in the Control Source Property.  Change the Name Property Value to SRL.

6. Change the Running Sum Property Value to Over All.

7. Create a Label control in the Page Header above the TextBox and write the Caption to SRL (for Serial Number).

8. Select all the Controls in the Detail Section and display the Property Sheet (F4 or Alt+Enter).

9. Change the Top Property Value to 0.  All the controls are shifted to the top edge of the Detail Section.

10. 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).

11. Display the Rectangle (F4) Property Sheet and change the Name Property Value to Box1.

12. Select the Send-to-Back option from the Arrange Menu to position the rectangle behind the text box controls.

13. Reduce the Detail Section height so that there is no empty space below the TextBox controls.

14. Display the VBA Code Module of the Report (ALT+F11).

15. 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
    

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

• Hexadecimal Numbering System
• Octal Numbering System
• Learn Binary Numbering System-2
• Learn the Binary Numbering System
• Scaling the Chart Object

Read More

Percentage on Report Summary

Percentage on Report Summary.

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:

1. Create a Report that lists the detailed values and includes a report-level summary (such as the total).

2. Insert a Text Box in the Detail section.

3. 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.

1. Import the Order Detail Table from Microsoft Access Sample Database: C:\Program Files\Microsoft Office\Office11\Sample\Northwind.mdb

2. Open a new Query in SQL View, without selecting a Table from the displayed list.

3. 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];
   

4. Save the Query named OrderSummary.

5. Design a Report 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.

6. Click the TextBox at the Report Footer to select it.

7. Display the Property Sheet (F4 or ALT+Enter) of the Text Box.

8. Change the Name Property Value to GTPrice (stands for Grand Total Price).

9. Write the expression =Sum([TotalPrice]) in the Control Source Property.

10. Select the Text Box at the right end of the Detail Section and display its Property Sheet.

11. Write the expression =[TotalPrice]/[GTPrice] in the Control Source Property.

12. In the Format Property, select the Percent format from the drop-down list.

13. Type 2 in the Decimal Places Property.

14. Save and Close the Report.

15. Open the OrderSummary in Print Preview and check the detail line percentage value calculated on the Report Footer Grand Total Price.

Creating Page-Level Totals.

Want to know how to calculate and display Page-wise control totals? You can learn it from here.

• Scaling the Chart Object
• User and Group Check
• User-Defined Data Type
• Budgeting and Control
• Change Secure DB to Unsecured

Read More