The Art of Assembling Pieces into a Tool

Introduction

Take the letters a, e, m, and t. These 4 letters can combine to make at least 5 different words: mate, meat, meta, tame, and – my favorite – team.

By putting different pieces together you can get useful outcomes. No surprise there but, if you think about what this means when you are in the world of programming, you can take relatively simple code snippets and turn them into powerful tools. This month, I will show you some relatively simple pieces of code and turn it into a useful tool, which will save you many keystrokes.

The Target – Creating a Clean Date and Time String

Our goal is to create a tool that is essentially a nicely-formatted date stamp that we can call whenever the need comes about.

For instance, say that we have a form letter that we would like to produce that includes a reference to an appointment at a certain date and time. We would like our text in the form letter to say that the appointment is scheduled for a certain date, followed by the word “at,” followed by the time of the appointment. Further, say that this appointment date and time is kept in a field “DateApptToApply.”

 

The Pieces and the Tool

We have 3 items we must manipulate:

1. A date
2. A time
3. The word “at”

The date and time is stored in a single field, and the word “at” is fixed text.

To put this together, we use the following

Format([DateApptToApply],"dddd mmmm d,
yyyy") & " at " & _ Format([DateApptToApply],
"h:mm AMPM")

(Please note that the above line can be put into a single line if you remove the underscore after the ampersand at the end of the first line.)

Let’s pull this apart to see what is happening.

First, we have a format statement for our date, Format([DateApptToApply], followed by a comma, and then format statement that returns the full words for the day and month

"dddd mmmm d, yyyy")

followed by the word “ at ” with space on each side followed by the time of the appointment an am or pm identifier

Format([DateApptToApply],"h:mm AMPM")

 

Taking our Tool and Making it very Easy to Use

So this is nice, but by itself is not terribly helpful, because we don’t want to continuously have to type out all the stuff just to get a particular statement. We want our tool to be easy to use and flexible. To achieve that end, we will turn it into a generic function and call it FormattedDateAndTime. It will have one input, a date and time to format.

Function FormattedDateAndTime(dtmIn As Date) As String
  FormattedDateAndTime = Format(dtmIn, "dddd mmmm d,
  yyyy") & _  " at " & Format(dtmIn, "h:mm AMPM")
End Function

With this, we just pass in any date and time and we get the output we want. For instance, if I go to the immediate window and type in
? FormattedDateAndTime(now())

I get back: Sunday June 8, 2015 at 3:22 PM, which is when I was composing this Wizard.

Conclusion
By using a technique that will turn pieces of a puzzle into a a function, which provides a tool ready to use whenever we need a properly formatted date and time stamp.
   

 

Trap of the Month: 
Different Column Headers

When you are exporting a report from Print Preview to Microsoft Excel, be aware that the Excel column headers will be whatever you have chosen for column headers in your underlying query; these may be different than what you chose to call the columns on your report.

To avoid this inconvenience, as you are creating a report, give your column names that are consistent with what you would like to see in Excel.