Microsoft Office 2000/Visual Basic Programmer's Guide   

Date Delimiters

When you work with date literals in your code, you need to indicate to VBA that a value is a date. If you don't, VBA may think you're performing subtraction or floating-point division.

For example, if you run the following fragment, the value that VBA assigns to the Date variable is not April 5, 1998, but 4 divided by 5 divided by 98. Because you're assigning it to a Date variable, VBA converts the number to a date, and prints "12:11:45 AM" to the Immediate window:

Dim dteDate As Date
dteDate = 4 / 5 / 98
Debug.Print dteDate

To avoid this problem, you must include delimiters around the date. The preferred date delimiter for VBA is the number sign (#). You can also use double quotation marks, as you would for a string, but doing so requires VBA to perform an extra step to convert the string to a date. If you rewrite the fragment as follows to include the date delimiter, VBA prints "4/5/98" to the Immediate window:

Dim dteDate As Date
dteDate = #4/5/98#
Debug.Print dteDate