The Access Wizard Newsletter
Tips, Tricks, and Traps for Access Users and Developers.

The Art of Assembling Pieces into a Tool


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.

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.


Tip of the Month: Date and Time should be Stored in a Single Field

If your roots are in Excel, which for most of us is where we started, your first instinct may be to store a time and date in two separate fields. Although you can do this, it makes life more difficult than it needs to be. When you want to put a date stamp on an event in Microsoft Access, all you have to do is use the now function.

When you think about it, a day has an infinite number of possibilities that may represent any day, both past and future. In a like manner, a time represents an infinite number of possible times of a day. If you are trying to store a particular date and time, it is best to it in a single field and format the output as I have shown in this Wizard.

Quick Links:

Custom Software Home

Access Wizard Archives

Our Services

© Copyright Custom Software
All Rights Reserved | Westford, MA 01886