CODEWITHSUNDEEP
vbams-access
Rick White
Rick White

Reputation: 41

vba format function incorrectly interpreting number as date

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 vba numeric equivalent of the date 5/1/1993.

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

Format("092 0250", "0000000000000") returns "092 0250", i.e. does not try to apply formatting.

Format("0930005", "0000000000000") returns "0000000930005" as expected.

The only solution to this that I have come up with so far is to code around using this function when the inbound string contains a space or use the IsDate function.

Upvotes: 4

Views: 552

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149297

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

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

will return 0000000034090

It will try and convert it to date or a number and if it is a valid date or number then it will show you the numeric equivalent of it. And if it is not a date or number equivalent then it will leave it as it is. I believe the reason for this is that the Format function is unable to ascertain the "Format" of the value and takes it as a string. It is similar to then saying Format("Blah", "0000000000000"). It is but obvious that you will not expect Format function to format it as 000000000blah.

Unfortunately I couldn't find any article in MS knowledge-base which explains why Format function behaves like this.

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

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

will give you the desired result 0000000930005

However if the numbers are separated by a HYPHEN or a DASH then the VAL function is useless. For that you will have to use REPLACE to replace the HYPHEN or a DASH with SPACE. However I doubt that you will be formatting numbers which have a HYPHEN or a DASH.

Upvotes: 4

Related Questions