Vba formatting function incorrectly interprets number as date

Question

Why does VBA interpret “093,0005” as a date?

In MS Access VBA, the following line: Format ("093 0005", "0000000000000") returns "0000000034090"

34090 is the numerical equivalent of vba date 5/1/1993.

In most cases, this kind of date assumption does not occur. For instance:

The format ("092 0250", "0000000000000") returns "092 0250", that is, it does not try to apply formatting.

The format ("0930005", "0000000000000") returns "0000000930005" as expected.

The only solution to this that I have encountered so far is the code around this function when the input line contains a space or uses the IsDate function.

Answer 1

This has nothing to do with Access. In any VBA, the format function will behave as if the text is either separated by the characters SPACE , HYPHEN , or DASH . So this is

 Debug.Print Format("093 0005", "0000000000000") Debug.Print Format("093/0005", "0000000000000") Debug.Print Format("093-0005", "0000000000000") 

will return 0000000034090

It will try to convert it to date or number , and if it is a valid date or number, then it will show you the numerical equivalent. And if it is not the equivalent of a date or number, it will leave it as it is. I believe the reason for this is because the Format function cannot determine the "Format" of the value and takes it as a string. This is similar to what Format("Blah", "0000000000000") . Obviously, you do not expect the Format function to format it as 000000000blah .

Unfortunately, I could not find any article in the MS knowledge base that explains why the Format function behaves this way.

The only way I discovered in the past to get around this is to use the VAL function to convert it to a number. So

 Debug.Print Format(Val("093 0005"), "0000000000000") 

will give the desired result 0000000930005

However, if the numbers are separated by a HYPHEN or DASH character, then the VAL function is useless. To do this, you will need to use REPLACE to replace HYPHEN or DASH with SPACE . However, I doubt that you will format numbers with HYPHEN or DASH .