Two different date formats

[manybeats]

Hi! I am pretty new to Access and am starting a new database - I'm just planning it out now. One of my tables will store information on technical reports (e.g. report name, file number, author and report date). Some of the reports are dated with day/month/year, but some only state month/year. So I am trying to figure out how best to capture this information. A few options I've considered:

1. If no day is on the report, simply choose the first day of the month. This is easiest solution, but then the date in my database won't exactly match the date on the actual report.
2. Have two fields for storing the date and the user has to choose which format to use. I see all sorts of problems with this, in terms of sorting and displaying the data.
3. Have a day, month and year field (i.e. three separate fields) and if there is no day, that field is just left blank. I'm thinking this might work but might be difficult to manage as well, in terms of sorting and displaying the info.

Any thoughts would be much appreciated.

Jenn

 

[Pat Hartman]

I would assume the first of the month for lack of any firm specification just because it's easy. But, the last day of the month might be more accurate if the report contains totals for a specified month, the last day is more accurate.

I would not go with any of the variable multi-column options. Here's a function to get the last day of any month:

Public Function LstDayMnth(InDate As Date) As Date
    LstDayMnth = DateSerial(Year(InDate), month(InDate) + 1, 0)
End Function

"assume" the first when you have just year and month and pass it to this function go get the last.

 

[manybeats]

Thanks for the quick response!

 

[mdlueck]

Any thoughts would be much appreciated.

Talk everyone into using YYYYMMDD format. That even sorts correctly.

 

[Pat Hartman]

Date data types always sort correctly unless you format them. Formatting them turns them into a string and then they sort like strings - character by character, left to right. Internally dates are stored as double precision numbers. Numbers always sort predictably . So the only string date format that sorts correctly is year, month, day order which should make sense if you understand how stupid computers are. And even then month must be numeric two digits and day must be two digits. Humans have a great capacity for making sense out of mis-spelled words and unusually formatted dates but computers do not.