Date Format: Year + 1

[RichNida]

Date Format not accurate
Format([Date],"yy")+1

The date format does not work correctly on all dates,
04-May-94 returns 95 (correct)
04-May-99 returns 100 (should be 00)
04-May-00 returns 1 (should be 01)
04-May-02 returns 3 (should be 03)
02-May-09 returns 10 (correct)

If I remove the “+1†the date format is correctly displayed.

The same problem also occurs with the Right Function:
Right(Year([Date]),2)+1

 

[Allen Browne]

Format() returns a string (text) value, such as 95, or 01.

To add 1 to it, Access must convert the text value into a number, and
perform the math. The result is a numeric type. Mostly with numbers we don't
include the leading zeros, so it (quite correctly) reports the value. 99
plus 1 really is 100.

If you want the result if the mathematical operation to be truncated to the
last 2 digits and formatted with a leading zero as text (not a number), you
need to specify that, e.g.:
Format((Format([Date], "yy") + 1) Mod 100, "00")
or
Format((Year([Date]) + 1) Mod 100, "00")

The crucial part is thinking clearly about the data types.
Text and Number are radically different, e.g.:
1 + 5 yields 6
"1" + "5" yields 15

(Hopefully you don't really have a field named Date, as that will cause you
grief too. In some contexts, Access will misunderstand it as the system
date.)

 

[RichNida]

Perfect, thank you Allen and thanks for pointing out the field naming for Date.

--
Richard

Format() returns a string (text) value, such as 95, or 01.

To add 1 to it, Access must convert the text value into a number, and
perform the math. The result is a numeric type. Mostly with numbers we don't
include the leading zeros, so it (quite correctly) reports the value. 99
plus 1 really is 100.

If you want the result if the mathematical operation to be truncated to the
last 2 digits and formatted with a leading zero as text (not a number), you
need to specify that, e.g.:
Format((Format([Date], "yy") + 1) Mod 100, "00")
or
Format((Year([Date]) + 1) Mod 100, "00")

The crucial part is thinking clearly about the data types.
Text and Number are radically different, e.g.:
1 + 5 yields 6
"1" + "5" yields 15

(Hopefully you don't really have a field named Date, as that will cause you
grief too. In some contexts, Access will misunderstand it as the system
date.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Date Format not accurate
Format([Date],"yy")+1

The date format does not work correctly on all dates,
04-May-94 returns 95 (correct)
04-May-99 returns 100 (should be 00)
04-May-00 returns 1 (should be 01)
04-May-02 returns 3 (should be 03)
02-May-09 returns 10 (correct)

If I remove the “+1†the date format is correctly displayed.

The same problem also occurs with the Right Function:
Right(Year([Date]),2)+1

 

[raskew via AccessMonster.com]

The DateAdd() function will eliminate those inaccuracies
you've reported, e.g.

? dateadd("yyyy", 1, "04-May-99")
5/4/2000

...and, if you want to retain the original format:

? format(dateadd("yyyy", 1, "04-May-99"), "medium date")
04-May-00

HTH - Bob

Perfect, thank you Allen and thanks for pointing out the field naming for Date.

Format() returns a string (text) value, such as 95, or 01.

[quoted text clipped - 33 lines]

The same problem also occurs with the Right Function:
Right(Year([Date]),2)+1