ACC2000: Using Format() and Month() Returns Incorrect Month (209191)

SYMPTOMS

Novice: Requires knowledge of the user interface on single-user computers.

If you use the Format() function on the result of a Month() function, Microsoft Access returns an incorrect result.

CAUSE

The Month() function converts a date to an integer. The Format() function is expecting a date serial number as an argument, but instead receives an integer.

MORE INFORMATION

The following sample function returns January instead of April:

=Format$(Month(#4/30/1999#), "mmmm")

To return just the name of the month, use the Format() function without the Month() function. For example, the following expressions returns April:

=Format(#4/30/1999#, "mmmm")

If the Month() function is required, use the MonthName() function to return the name of the month. The following example demonstrates how to use the MonthName() function:

=MonthName(Month([datefield]))

REFERENCES

For more information about the MonthName() function, click Microsoft Access Help on the Help menu, type MonthName function in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about the Format() function, click Microsoft Access Help on the Help menu, type format property in the Office Assistant or the Answer Wizard, and then click Search to view the topic.