Date Format after 1929

[Bart]

I have a very simple database recording the dates on which a number of 19th and 20th century seamen served on a number of ships.

I would like to store and display dates in the format dd-mmm-yyyy; eg 05-Sep-1918.
When I key in dates in that format, all works fine for 19th-century dates and for 20th-century dates up 31-Dec-1929.


But not for any date after that. With dates from 1-Jan-1930 on the system will not display the ‘19’.

It clearly does store the ‘19’ because date processing such as sorting works correctly.
But it looks ridiculous when the screen displays the information that a certain man served on a certain ship from ‘21-Sep-1928’ to ‘19-Jan-30’.

What is going on here? What is so special about dates after 31-Dec-1929?
I'd be grateful for any insight.

[alansidman]

Here is an explanation. After reading change the format to a specific format

https://support.office.com/en-us/art...5-c7293eddbb43

[John_G]

I would like to store and display dates in the format dd-mmm-yyyy; eg 05-Sep-1918.

Dates are not stored in a format. They are stored internally as a number, with the part before the decimal point representing the date, and the part after the decimal place representing the time. "format" is for display purposes only. To consistently display the dates in the format you want, use the format function: Format(datevalue,"dd-mmm-yyyy"), which will always show the 4-digit year.

[Bart]

Brilliant! Worked like a charm. Hours of exasperation now resolved. I'm very grateful to you two guys.

Here's a much smaller problem I have: Storing 'unknown' in a date field. D'you have any neat tricks I wouldn't easily think of myself?

[Bart]

Hey, not so fast! I'm exasperated again.
In datasheet view all is fine, the 1929/30 nonsense is gone.
In the results of my SQL queries, still fine, no 1929/30 nonsense.
But then I process the results of those SQL queries in Visual Basic in order to make them into html files.
When I view those files with the html browser the 1929/30 nonsense is back again.
How is that possible?

[Minty]

The format forces the output to a string - so I suspect the format is being 'Interpreted' by the HTML rendering ?
What are you VBA queries looking like ?

[John_G]

But then I process the results of those SQL queries in Visual Basic in order to make them into html files.

You would still have to use the Format() function in your HTML conversion VBA to display the dates the way you want them to be. Using the Format() function doesn't change anything in the database data - it only affect how you see it.

[Bart]

@Minty
The output that comes from the Visual Basic module is an html file in which the post-1929 dates are unfortunately without the ‘19’


@John_G
Let me see if I understand you.
In my VB code I already have a line like this:
a.WriteLine ("<TD><P CLASS=AG>" + Str(crex!begindate) + "</TD>")
Somehow I need to work into that line of code the following additional thing:
Format(datevalue,"dd-mmm-yyyy")
Is that a correct understanding? If so, I’m sorry to seem so helpless, but how exactly?

[John_G]

Change Str(crex!begindate) to format(crex!begindate,"dd-mmm-yyyy")

The format() function returns a string value, so you don't need str().

[Bart]

Perfect.
Dare I say it, problem now solved!