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 into 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 and compile the Code.

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 not correct in your case change it on the Regional settings on your Computer.

Scope of this Function.

The Date2Text() Function can be placed in Text-Box on Report Header, use it on Query’s Date-Field 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 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, 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 Text Boxes.  When the Mouse Move Event occurs the Text-Box 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 Text-Box Name as 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 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:

Complexity of Requirements.

Assume that you are developing a database for a movie ticket booking Application and needs around 350 or more text boxes on the Form, for a graphical design of the seating arrangement.  Each Text-Box represent a single seat in the cinema hall, in an arrangement of several rows & Columns (i.e. each row have several seat positions) and each Seat is having a unique identity number (that is text box name), indicating it's 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 it's 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 to look for his choice of Seat Numbers and book the Seat(s).

A simple Form with several Text Boxes and a label on the top is given below to get an idea of sample arrangement of Text Boxes, to try out methods to solve this 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 do we manage to get the Text-Box Name on the Mouse Move Event and pass the name as a string parameter to the =myFunction() Function, placed in 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 off work.  Besides that, if any change of arrangement of Seats or reworking of the Seat Numbering scheme become 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 Text-Box, after a brief delay (the delay is not acceptable), the Seat Number is displayed from Control tip text property by 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 parameter to the Function.  Besides displaying the Text-Box Name on the designated Label Caption and the Function may have other issues in the program to take care off 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 Text-Box name, say Text1, from the Name Property, when the mouse moves over that Text-Box and pass the name as parameter to the Calling Program?   Remember, the Mouse Move Event fires repeatedly, at every mouse-point coordinates on the text box (or on any other control it moves)  and this Event have 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 Road-Blocks.

There are times that we face road-blocks on solving issues, when conventional programing approaches doesn't give the correct solutions.  But such issues can be easily handled by few lines of Code through Object Oriented Programming.  This is a classic example, easy to understand and does the job with 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 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 Text-Box 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 Object are declared in txt and frm object Variables with Private scope, respectively.  The txt Property is declared with WithEvents key word to capture Events originating from Text Boxes 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 Text Box Object's common feature for 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 over-writes the Text Box's Mouse Move Event Property Value. 

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

We can get the Text-Box name from the txt Property.  The Text-Box 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 Text-Box 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() any more. 

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

The simple RunMouseOver() Function Code will be presented later in 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 takes only Text Box controls.  The Form Object and Text-Box 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 Class Module instance of ClsTxt for the first time. In the next step ClsTxt Object instance F is added to the Collection Object, as it's 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 Text Box Object, with a different internal reference, related to each Text Box added to the Collection Object as it's Item.

This process repeats for all the Text Boxes 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 stays 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 Text-box 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 the 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 it 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 text-box 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 Text-box Name.

The RunMouseOver() Function can be modified to pass the Form's Name as 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 Text Boxes the same procedure is repeated for that Text Box Object instances 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 ready for action.  So everything controlled by the Object oriented Programming and happens dynamically.  This sample database is uploaded as 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:

Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

http://clicky.com/101199826
Blog Directory Visit blogadda.com to discover Indian blogs

Search

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email

Labels

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

Featured Post

Running Sum in MS-Access Query

Introduction. We are familiar with creating Auto-Numbers in Query Column, through an earlier Function created with the Name QrySeq(), publi...

Labels

Blog Archive

Recent Posts