Microsoft Access asks “Enter Parameter Value” when using the VB/VBA function FormatDateTime()

System Requirements:

  • Microsoft Access

The Problem:

When using an Integer based formatting value when calling the VB/VBA function FormatDateTime() as part of a statement in the Microsoft Access Expressions builder Access fails to process the function call correctly when attempting to preview the form or report. Instead,Access requests you enter a value for the variable ‘formatdatetime’ and ignores the correctly formatted function call as pictured below:

FormatDateTime Error

An example of the the function call which may produce this error would be:

The above function call is designed to take the input date and time string and reformat the output to display only the time in a 24 hour clock format (vbShortTime). The integer value options are predefined as per the table below.

Constant Value Description
vbGeneralDate 0 Display a date in format mm/dd/yy. If the date parameter is Now(), it will also return the time, after the date
vbLongDate 1 Display a date using the long date format: weekday, month day, year
vbShortDate 2 Display a date using the short date format: like the default (mm/dd/yy)
vbLongTime 3 Display a time using the time format: hh:mm:ss PM/AM
vbShortTime 4 Display a time using the 24-hour format: hh:mm

The Fix:

This is an interesting one, because I haven’t conclusively been able to track down why it is doing it in this case. I have increasingly been seeing similar problems with integer based function parameter calls made to and by ADO and VB objects in ASP and VBA on completely unrelated, disparate systems on both workstations and servers and with different Windows/Office combinations on them.

While the error in the case of integer specification of the ADO cursor type .CursorType = i (where i = -1 through 3) is due to a mal-configured call to C:\Program Files\Common Files\System\ado\, I have not managed to find a similar symptom for the formatdatetime issue outlined above.

There is a very simple fix however, don’t use the integer value, use the full format value string. So, the example of:
FormatDateTime([database_record],4) would instead become FormatDateTime([database_record],”hh:nn”)

The example of:
FormatDateTime([database_record],2) would become FormatDateTime([database_record],”mm/dd/yy”)

This should get you through the Access ‘error’ and force it to realise that you’re calling a VB function and not a private one.