Partial Dates (Office 07)

Topic

New Reply

Hi,

I have a table of historical dates which are frequently only partial dates. For example I might only know that an event happened in June 1995 Or perhaps I only know the year.

I am hoping to sort these dates (I suspect that there may be a great number of ways of sorting these – but the exact order is not critical, as long as its consistent. So if for example all partial dates containing just 1995 come before full dates for that year or after does not particularly bother me.

I had planned to use a date field if I had a full date, and a number field (or fields) if I had a part of the date, and then create an expression to resolve it.

Before I start however I thought it might be a problem with an obvious solution. Unfortunately Google and groups searches have not enabled me to find anything, so any suggestions welcome.

Thanks

Colin

Reply | Quote

Replies

I would store the data in a text field in Access. Then, to sort on the values, create a function that converts the date into a full date. For example, if the value is just 1995, convert it to 1/1/1995. If the date is 8/1995 or 8/95, convert it to 8/1/1995. The test would be to see how many slashes are in the date to determine if just the day or a month and day need to be added. With this method you would also need to add some input validation to make sure people were entering dates or you could sort anything that didn’t convert to a date to the top or bottom by having your function convert unrecognized values to 1/1/0000 or 12/31/9999.

You would call the function from within the query that retuns the data. It would be placed in the ORDER BY clause and the input parameter for the function would be the date field you want to sort on.

Reply | Quote

That sounds a good way.

I can usually cope with setting this sort of thing up, but ican NEVER think of the ways to do it in the first instance.

So thanks for that idea. Seems a great way to do it

Colin

Reply | Quote

What you could also do in a text field is to have yyyy when just the year is entered, yyyymm when the month and year is entered, then yyyymmdd when the whole date is entered. Sort on this field.

You can have an extra field as a date field if it suits as has been described.

Reply | Quote