Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

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
Share:

Call Function From MouseMove Event Property

Introduction.

This is about running a User-Defined Function (say =myFunction(Parameter)) on the Mouse Move Event Property of TextBoxes.  The difficult part is that when the Mouse Move Event occurs the Event running TextBox name must be passed as Parameter to the function dynamically.

This question was asked in an Access User's Forum (www.accessforums.net), in the Forms Category of Posts, by a member, seeking suggestions for a solution.  A demo database was posted by me there, twice on page 5, but the last one is the final version. 

I thought it will be useful to my readers and presented here for you, with details of this difficult requirement and how this Object Oriented Programming approach solved the puzzle.

Manual Option.

This is easy to set up if it is manually entered =myFunction("Text1")  on each Text Box's Mouse Move Event Property

But, the requirement is to pass the TextBox Name as a parameter dynamically to the Function.  It means that we should somehow get the Text Box Name from the Mouse Move Event and pass it as a parameter to the function, placed on the same Mouse Move Event Property.

To get to know the real situation that demands this method, digest the following requirement of an Access Application:

The complexity of Requirements.

Assume that you are developing a database for a movie ticket booking Application and need around 350 or more text boxes on the Form, for a graphical design of the seating arrangement.  Each TextBox represents a single seat in the cinema hall, in an arrangement of several rows & Columns (i.e. each row has several seat positions) and each Seat is having a unique identity number (that is the text box name), indicating its position in the auditorium, like Row-A, Seat No.5 (A5) or B1, etc. The text box text will show Booked or Vacant depending on its current status.

The idea is when the mouse moves over the textbox (Seat) it should display the Seat Number (A5, or B1, etc.) on a dedicated Label on the Header or Footer Section of the Form, to help the customer look for his choice of Seat Numbers and book the Seat(s).

A simple Form with several TextBoxes and a label on the top is given below to get an idea of textbox arrangements and try out this method to solve the problem.

PS: The technical details presented above may have some lapses or may form suggestions in the minds of the reader.  That is not important, the core point is how we manage to get the TextBox Name on the Mouse Move Event and pass the name as a string parameter to the =myFunction() Function, placed on the Mouse Move Event Property.

Why Manual Method not Acceptable.

So, writing =myFunction("A5") or =myFunction("B1") etc., in each one of  350 Text Box's Event Property is lots of work.  Besides that, if any change of arrangement of Seats or reworking of the Seat Numbering scheme becomes necessary, then all the text box Properties have to undergo manual changes. 

Another option available is to set the Control Tip Text Property with the Text Box Name. When the mouse pointer rests on the TextBox, after a brief delay (the delay is not acceptable), the Seat Number is displayed from the Control tip text property of the System.  Modifying the Control Tip Text Property is easy and can be done dynamically on the Form_Load() Event Procedure. 

But, the database designer insists on passing the Text Box Name as a parameter to the Function.  Besides displaying the TextBox Name on the designated Label Caption and the Function may have other issues in the program to take care of as well, on the Mouse Move Event. 

The Difficult Question.

Even though it sounds like a simple issue, the difficult question is how do we get the TextBox name, say Text1, from the Name-Property of the running form, when the mouse moves over that TextBox, and pass the name as a parameter to the Calling Program?   Remember, the Mouse Move Event fires repeatedly, at every mouse-point coordinate on the text box (or on any other control it moves)  and this Event has some default parameters: Button, Shift, X, and Y coordinates of the Mouse Pointer on the Control.  But not the Control Name among them.

The Programming Roadblocks.

There are times that we face roadblocks to solving issues when conventional programming approaches don't give the correct solutions.  But such issues can be easily handled by a few lines of Code through Object-Oriented Programming.  This is a classic example, easy to understand, and does the job with a few lines of code.

Access Class Module Objects.

We have already covered earlier the fundamentals of Access Class Modules and Objects-based programming.  If you are not familiar with stand-alone Access Class Modules and Objects then the links are given at the bottom of this page for you to start learning the basics.

The Easy Solution.

To solve the above-narrated issue we have used a few lines of code in the Access Class Module Objects (both Form and stand-alone Class Modules) and used Collection Object to organize several instances of the Class Module Objects, rather than using Arrays.

The General-purpose TextBox Object Class Module: ClsTxt Code:

Option Compare Database Option Explicit Private WithEvents txt As Access.TextBox Private frm As Access.Form Public Property Get pFrm() As Access.Form Set pFrm = frm End Property Public Property Set pFrm(ByRef vNewValue As Access.Form) Set frm = vNewValue End Property Public Property Get pTxt() As Access.TextBox Set pTxt = txt End Property Public Property Set pTxt(ByRef vNewValue As Access.TextBox) Set txt = vNewValue End Property Private Sub txt_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single) '------------------------------------------------ 'The first MouseMove Event, of each TextBox, 'comes into this sub-routine. 'The MouseMove Event Property is set with the Function: '"=RunMouseOver('Textbox_Name','Form_Name')" 'with the TextBox & Form Names as Parameters. 'Subsequent MouseMove Events Calls the Function 'directly from Standard VBA Module1, 'control will not come into this sub-routine, any more. '------------------------------------------------

txt.OnMouseMove = "=RunMouseOver('" & txt.Name & "')" End Sub

Two Class-Module Properties, the Access.TextBox and Access.Form Objects are declared in txt and frm object Variables respectively, with Private scope, respectively.  The txt Property is declared with the WithEvents keyword to capture Events originating from TextBoxes on Form. The next twelve lines of Code are for assigning and retrieving objects in Text Box and Form Properties with Set/Get  Property Procedures.  This will prevent direct access to the Class Module Properties txt and frm from outside.  Up to this point, it is the TextBox Object's common feature of assigning and retrieving values to and from the Object Variables.  The frm Property is not used here.

The sub-routine part is what we are interested in.  Any number of Text-Box-based Event Procedure sub-routines can be written here rather than directly on the Form's Class Module.

The txt_MouseMove() Event.

The Text Box's first Mouse Move Event transfers control into the txt_MouseMove() Subroutine.  There is only one executable statement in the sub-routine, that overwrites the Text Box's Mouse Move Event Property Value. 

txt.OnMouseMove = "=RunMouseOver('" & txt.Name & "')"

We can get the TextBox name from the txt Property.  The TextBox Mouse Move Event Property (initially set as "[Event Procedure]" in the Form_Load() Event Procedure) is replaced with the Function "=RunMouseOver('" & txt.name & "')" and passes the TextBox name as string Parameter.  The subsequent Mouse Move Events will call the RunMouseOver() Function in Standard Module, from the Mouse Move Event Property, and never comes back to the above sub-routine txt_MouseMove() anymore. 

So, the first Mouse Move Event on any Text Box will do the trick and other TextBoxes will wait for their turn for a Mouse Move Event to take place.

The simple RunMouseOver() Function Code will be presented later on this page.

Form3 Class Module Code.

The Form's (Form3) Class Module VBA Code is given below:

Option Compare Database
Option Explicit

'Declare Class ClsTxt as Object F
Private F As ClsTxt
'Declare Collection Object as C
Private C As Collection

Private Sub Form_Load()
Dim ctl As Control

Set C = New Collection 'instantiate Collection Object

For Each ctl In Me.Controls 'scan through the controls
   If TypeName(ctl) = "TextBox" Then ' Take only Text Boxes
        Set F = New ClsTxt 'instantiate ClsTxt Class Object
        
            Set F.pFrm = Me 'Assign Form to pFrm Property
            Set F.pTxt = ctl 'Assign TextBox to pTxt property
            'enable mouse move event
            F.pTxt.OnMouseMove = "[Event Procedure]"
            
        C.Add F 'add ClsTxt Object to Collection
        
        Set F = Nothing 'remove the ClsTxt object instance from memory
    End If
Next

End Sub

Private Sub Form_Unload(Cancel As Integer)
    'Erase the Collection Object when Form3 is closed.
    Set C = Nothing
End Sub

In the global declaration area, Class-Module ClsTxt is declared as Object F and Collection Object as C.

In the Form_Load() Event Procedure, we scan through Form3 controls and take only TextBox controls.  The Form Object and TextBox controls are assigned to the F.pFrm and F.pTxt Properties of ClsTxt Object.

The F.pTxt Object's OnMouseMove() Event Procedure is enabled so that when it happens the control goes to the txt_MouseMove() sub-routine of the Class Module instance of ClsTxt for the first time. In the next step ClsTxt Object instance, F is added to the Collection Object, as its Item.  In the next step, the ClsTxt Object instance F is cleared from memory.  A new F object instance is created for the next Text Box.  This is necessary to identify each instance of TextBox Object, with a different internal reference, related to each Text Box added to the Collection Object as its Item.

This process repeats for all the TextBoxes on Form3.

When the Form is closed the Form_Unload() Event executes and the Collection Object is cleared from Memory.

When Form3 is open these initializing steps are performed and all the Text Box Controls are enabled with the Mouse Move Event, added to the Collection Object, and stay in memory till Form3 is closed.  Each Text Box's Mouse Move Event is handled by their respective ClsTxt Object instance added in the Collection Object.

The RunMouseOver() Function Call.

When the user moves the mouse over a Text Box (say Textbox name A1) for the first time the Mouse Move Event executes and calls the txt_MouseMove() Event Procedure in the ClsTxt Object instance, for that Text Box, in the Collection Object item.  In this procedure, TextBox's MouseMove Event Property is modified and inserted with the =RunMouseOver("A1")  Function with the Text Box name A1 as Parameter.

The second Mouse Move Event onwards the event calls the RunMouseOver() Function from the Standard Module1.  The VBA Code of this Function is given below.

Option Explicit

Public Function RunMouseOver(strN As String)
    Screen.ActiveForm.Controls("Label0").Caption = strN

End Function

The RunMouseOver() Function receives the textbox name as parameter.  The statement addresses the Label0 control, directly through the Screen Object ActiveForm route and changes the Label's Caption with the Mouse Moved Textbox Name.

The RunMouseOver() Function can be modified to pass the Form's Name as a second parameter and can be used to address the Label0 control as Forms(strForm).Controls("Label0").Caption = strN.  This is avoided to keep the parameter expression simple.

When the Mouse is moved over other TextBoxes the same procedure is repeated for that Text Box Object instance in the Collection Object.

When Form3 is closed the Collection Object instance C, containing all Text Box's ClsTxt Class Object instances, is cleared from memory.

The Function RunMouseOver() assigned to Text Box's Mouse Move Event Properties are cleared (as they are assigned dynamically) and the Property will remain empty.

Next time when Form3 is open everything falls into place again and is ready for action.  So everything is controlled by Object-oriented Programming and happens dynamically.  This sample database is uploaded as a solution to the Access User's Forum Page 5, where several alternative options are suggested by other members of the Forum.  You may visit this Group for suggestions to solve your issues and for help on matters related to Queries, Reports, Forms, etc.

The Demo Database is attached and may Download and try it out yourself.



Class Module Tutorials.

  1. MS-Access Class Module and VBA
  2. MS-Access VBA Class Object Arrays
  3. MS-Access Base Class and Derived Objects
  4. VBA Base Class and Derived Objects-2
  5. Base Class and Derived Object Variants
  6. Ms-Access Recordset and Class Module
  7. Access Class Module and Wrapper Classes
  8. Wrapper Class Functionality Transformation
  9. Ms-Access and Collection Object Basics
  10. Ms-Access Class Module and Collection Object
  11. Table Records in Collection Object and Form
  12. Dictionary Object Basics
  13. Dictionary Object Basics-2
  14. Sorting Dictionary Object Keys and Items
  15. Display Records from Dictionary to Form
  16. Add Class Objects as Dictionary Items
  17. Update Class Object Dictionary Item on Form
Share:

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code