Date2Text and Text2Date Functions

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.

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

Introduction

Normally, MS-Access Reports can run into several pages and normally we give page numbers on each page along with the total number of pages as an indicator.  To do this,  a TextBox is added to the  Page Footer of the Report and an expression like the following is entered into the Control Source property:

=”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 keep writing these expressions repeatedly every time when a new report is designed.  If you are a VBA enthusiast and using VBA routines in your applications, then this kind of action can be automated with the use of User-Defined Functions and call the function from the Text Box's Control Source property to quickly insert this information on the Report.  I have written two functions for this purpose and if you are interested to take a look at them then click here.

When you are working with VBA routines in Standard Modules or Class Modules you can see that certain groups of statements or actions are repeated at different places, which are useful across different Applications.  They can be customized and written in the form of public functions and added to your User-Defined Function Library so that they can be called with a one-line statement.  The lengthy VBA Routines can be compressed this way.  There is a Blog Post related to this topic: MS-Access and Reference Library.

Coming back to the Page indicator topic we will try to display the page continuity information in the Page Footer of the Report with a difference.  The Report can be a single page or can have several pages. When the Report has more than one page, then on the first-page footer the following label should appear: Continued on Page 2.

On the next page (i.e., on page 2) Continued on Page 3 and so on till the last but one page.  This label should not appear on the last page.  If the report has only one page then this label should not appear at all.

With the first example given at the top of this Article, the Report with only one page will 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; like 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 they 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

Introduction.

When Computer Reports; like Accounting Ledger Statements or Purchase Invoices are printed with closely positioned lines. It is easy to read if the alternate line background is shaded with a light color.  Normally, pre-printed stationery is used for this purpose. Line-Printers will print 6 lines of data within a one-inch space vertically.  To print headlines in bold the double-strike method is used (the hammer will strike a character twice) and this is the only enhanced printing style available, as far as the Line Printers are concerned.

The Line-Printer-based Reports were designed on graph-paper-like sheets and write codes to position Headings, data lines, and summaries to position them properly on Report stationery.

A4 (8.5 x 11 inch) size paper will have 66 lines of print positions vertically. Out of this, one inch top and half an inch bottom margin space are left out while printing the alternate data print positions with green color like the sample image given below:

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

This was general information about computer stationery. Reports are designed for Line Printers keeping these specifications in mind.  If necessary, we can design reports with detailed section height and the text box control's height in one-sixth of an inch in plain paper stationery.

Light Shading of Alternate Report Data Lines

We will try a small trick to print alternate print lines with the shaded background so that users can read the contents very easily. We don't need pre-printed stationery for this and we will prepare shaded background while printing the report itself.

We can design a Quick Report and write a few lines of VBA Code on the class module of the Report. If you already have a Report with closely printed detail lines you can skip to step 3 given 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:

   

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 Text Box on the left side to display Serial Numbers on the Report Lines.  Drag the heading lines and position them to the right.

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

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

7. Create a Label control at the Page Header above the Text Box and change the Caption to SRL (for Serial Number).

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

9. Change the Top Property Value to 0.  All the controls will be shifted and positioned at 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 Property Sheet of Rectangle (F4) and change the Name Property Value to Box1.

12. Select the Send-to-Back option from 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 Binary Numbering System
• Scaling the Chart Object

Read More

Percentage on Report Summary

Introduction.

We have worked with a Query to solve this problem earlier in the Blog Post: Percentage on Total Query.  This time let us see how it is done on a Report.  Our task is to show a detail-line-wise value’s percentage on Report Summary Total.

The solution is simple.  Create a Report with some values in it with a Report level summary.  Add a Text Box in the detail section and write an expression to divide the Report Summary Value into the detail level value.

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 with the name OrderSummary.

5. Design a Report (as shown in the image given below) with the Detail Section and Report Footer summary controls using the OrderSummary as Source.

   

6. Click on the Text Box at the Footer of the Report 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 selects 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 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