Access VBA Date Conversion: Parse, Format, and Validate

In Access VBA, use CDate to convert a value VBA already recognizes as a date, DateSerial to build a date from year, month, and day parts, and Format$ only to create display text. The safest choice depends on whether the input is a date value, text, or separate numeric parts; importantly, formatting a date does not convert it into another date.

What an Access date value represents

Access stores a date and time together in a Date/Time field, even if the field is displayed as a date alone. The time portion can be midnight, a specific time, or a fraction of a day. A field’s Format property changes how its value appears; it does not remove the time or change the underlying value. For example, a value shown as 4/8/2025 could still include a time such as 2:30 PM.

In VBA, a variable declared As Date holds a date/time value. A Variant can also contain a Date subtype, along with other kinds of values such as text, numbers, or Null. Date conversion therefore involves two separate questions: can the input be interpreted as a date, and should any time component be kept?

VBA dates cover dates from January 1, 100 through December 31, 9999. A date conversion can fail if text is invalid, a number is outside the supported range, or an expression attempts to create an impossible date. It is usually better to handle those cases deliberately than to assume every value in a table is valid.

When to use CDate, DateValue, and TimeValue

CDate(expression) converts an expression to a Date when VBA can interpret it as one. It is useful for values already known to follow the computer’s date and time conventions, such as a Date field value or a familiar date string. For example, a string like 4/8/2025 may mean April 8 or August 4 depending on regional settings, so it is not a safe universal format for imported text.

DateValue(expression) returns the date portion, with the time set to midnight. TimeValue(expression) returns the time portion. Both interpret text according to the system’s regional conventions, so they are not a substitute for a defined parsing rule when data comes from a file, website, or another locale.

Use the conversion that matches the intended result:

Use CDate when the input is a valid date/time value or a string whose interpretation is unambiguous in the current environment.
Use DateValue when you explicitly want to discard the time part.
Use TimeValue when only the time matters.
Use DateSerial when you have separate year, month, and day numbers.

For instance, if a field contains a genuine Date value, assigning it to a Date variable is preferable to converting it to text and parsing that text again. Converting a Date to a string and back introduces unnecessary locale and formatting risks.

Build dates from numeric parts with DateSerial

DateSerial(year, month, day) constructs a Date from three numeric arguments. It is a good choice when the source provides separate fields or when you have parsed a defined text format. Unlike parsing a date string, its argument order is explicit: year first, month second, day third.

One important detail is that DateSerial normalizes out-of-range month and day values rather than serving as a strict calendar validator. For example, a day value beyond the number of days in a month can roll into the following month. If an input must be rejected unless it is a real calendar date, validate the parts before accepting the result.

The following function accepts text strictly in yyyy-mm-dd form and returns False for malformed or out-of-range dates. It is intended for a date-only value, not a date with a time or a time-zone offset.

Public Function TryParseIsoDate(ByVal text As String, ByRef parsed As Date) As Boolean
    Dim parts() As String
    Dim yearPart As Long
    Dim monthPart As Long
    Dim dayPart As Long

    On Error GoTo InvalidDate

    ' Require the exact yyyy-mm-dd shape.
    parts = Split(text, "-")
    If UBound(parts) <> 2 Then GoTo InvalidDate
    If Len(parts(0)) <> 4 Or Len(parts(1)) <> 2 Or Len(parts(2)) <> 2 Then GoTo InvalidDate

    yearPart = CLng(parts(0))
    monthPart = CLng(parts(1))
    dayPart = CLng(parts(2))

    ' Check the calendar components before DateSerial can normalize them.
    If yearPart < 100 Or yearPart > 9999 Then GoTo InvalidDate
    If monthPart < 1 Or monthPart > 12 Then GoTo InvalidDate
    If dayPart < 1 Or dayPart > Day(DateSerial(yearPart, monthPart + 1, 0)) Then GoTo InvalidDate

    parsed = DateSerial(yearPart, monthPart, dayPart)
    TryParseIsoDate = True
    Exit Function

InvalidDate:
    TryParseIsoDate = False
End Function

For example, calling this function with 2024-02-29 succeeds because 2024 is a leap year; 2025-02-29 returns False. The function uses an error handler as a final safeguard for text that cannot be converted to numbers. In a form, you can call it before saving an imported value and show a validation message if it returns False.

Formatting a date is not converting it

Format$ returns a String, not a Date. Use it when you need a predictable display or export representation, but keep the original Date value for calculations, sorting, and date comparisons. In VBA date format patterns, mm represents the month when used with a date, while nn represents minutes in a time pattern. For example, formatting a Date variable with yyyy-mm-dd hh:nn:ss produces text such as 2025-04-08 14:30:00.

That text is useful for a label, a log, or a text file with a specified format. It should not be confused with an Access Date/Time field. If you assign the formatted string to a text field, it remains text; if you later convert it back, the result depends on how that text is parsed.

For normal forms and reports, set the control or field’s Format property to control appearance while retaining a real date value. A format such as Short Date can display the date according to regional settings. When a report needs a particular audience-facing style, a format setting is generally better than storing a preformatted string in the table.

Handle Null, empty, and invalid values

Access databases often contain Null values, which mean that no value is present. Null is not the same as an empty string, zero, or a valid date at midnight. Calling a conversion function without checking for Null can result in an error or an unexpected result, depending on the expression and context. In table or form code, test for Null before conversion.

IsDate(value) can help determine whether VBA recognizes a value as a date, but it follows VBA’s interpretation rules and does not prove that text follows your required input format. A locale-ambiguous string may pass IsDate and still be interpreted differently on another computer. For strict imports, parse the documented pattern explicitly, as in the yyyy-mm-dd example above.

A general conversion routine can be useful when varied values are expected, but make its accepted inputs clear. Decide whether numeric values should be treated as date serials, whether time-only strings are allowed, and what to do with Null or blank text. Silently turning invalid input into today’s date, for example, conceals a data error rather than resolving it.

Use parameters when filtering dates in Access queries

A common date-conversion problem appears when building SQL by joining a date into a text string. Date literals enclosed in number signs are part of Access SQL, but the text representation of a date can be affected by regional conventions. Concatenation can also mishandle times and makes the query harder to maintain.

For DAO code, use a typed parameter instead. This example creates a temporary QueryDef and assigns an actual Date value to its parameter:

Public Sub ShowOrdersFromStartDate()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim startDate As Date

    ' Use an explicit date rather than a locale-dependent text literal.
    startDate = DateSerial(2025, 1, 1)
    Set db = CurrentDb
    Set qdf = db.CreateQueryDef("", _
        "PARAMETERS pStart DateTime; SELECT * FROM Orders WHERE OrderDate >= [pStart];")
    qdf.Parameters("pStart").Value = startDate

    ' The QueryDef can now be used to open or otherwise process the results.
End Sub

The example assumes a table named Orders with a Date/Time field named OrderDate. DAO is available in Access VBA projects; if a project reports that DAO types are not defined, check the project’s references. For an end date that should include every time on that calendar day, a range from the start date up to, but not including, the next day is often safer than comparing to midnight at the end date. Use parameters for both boundaries.

If code updates records rather than merely reading them, save a copy of the database before running it. VBA macros and code changes cannot be undone with Ctrl+Z, so test against a copy or a small set of records first.

Common conversion mistakes and how to avoid them

Using a display string as stored data: keep dates in Date/Time fields; format them for display only.
Assuming month/day order: strings such as 03/04/2025 are ambiguous. Define the incoming format and parse it accordingly.
Forgetting the time component: a date shown without a time may still include one. Use DateValue if the time must be discarded.
Trusting DateSerial to reject invalid days: check the month’s day limit before constructing a strict calendar date.
Comparing a date/time field to midnight unintentionally: a value at 3 PM is later than that day’s midnight. Choose comparison boundaries that match the intended period.
Converting Null as if it were blank text: test Null separately and decide whether missing input is allowed.

Also distinguish conversion from date arithmetic. Functions such as DateAdd calculate a new date from an existing date and an interval; they do not parse text. Functions such as DateDiff compare dates over an interval. Use those only after you have a valid Date value and have decided how time-of-day should affect the result.

Choosing a method for your source data

For values already stored in Access Date/Time fields, retain the Date value and assign it to a Date variable when needed. For user-entered text in a form, validate it and give the user a clear expected format. For imported text with a documented standard such as yyyy-mm-dd, parse the components and validate the calendar date rather than relying on the machine’s regional settings. For separate numeric columns, check the ranges and use DateSerial.

For output, use a control format or Format$ according to whether the result is still meant to be a date or is specifically meant to be text. For query criteria, pass Date values as parameters. These choices keep the stored value, the displayed representation, and the rules for interpreting incoming text from being accidentally mixed together.

Frequently Asked Questions

How do I convert text to a date in Access VBA?
Use CDate only if the text is valid and its date order is clear under the current regional settings. For a fixed input format, parse its parts and construct the result with DateSerial after validating them.

Does Format convert a string or value to a Date?
No. Format and Format$ return formatted text. Use CDate, DateValue, or DateSerial for conversion, depending on the source and the intended result.

How can I remove the time from an Access date?
Use DateValue on a valid date/time value to get the date portion at midnight. If the time must be preserved in the database, do not overwrite the original field merely to change how it displays.

Why does CDate give different results on another computer?
Text dates can be interpreted according to regional date settings. A numeric-looking date with month and day both at or below 12 may therefore be read in a different order. Use a defined input format and explicit parsing for portable results.

Can I compare an Access date field with a string in SQL?
It is better to use a typed query parameter containing a Date value. This avoids relying on date-literal text conventions and makes the intended value explicit.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.