Re: Multiple date formats in a Table

• Previous message: Andrew Smith: "Re: converting record info to fields"
• In reply to: harvestman: "Multiple date formats in a Table"
• Next in thread: John Nurick: "Re: Multiple date formats in a Table"
• Messages sorted by: [ date ] [ thread ]

Date: Wed, 7 Apr 2004 22:00:52 +0100

>From what you say I assume that you currently have the dates stored in a
text field - is that correct?

Access stores dates as numbers where 1 represents 31 Dec 1899 so today is
stored as (7 Apr 2004) 38084. If you change your text(?) field to a date
field then I think that Access will do its best to convert the text into a
date. However, text that it does not recognise as a date will get discarded.
So 2004 or Apr-2004 could well get lost.

In any case, a date field could not store just the year 2004 - it would have
to store a specific date in that year. You could decide to store it as 1 Jan
2004, but then you would not be able to tell if that really meant 1 Jan 2004
or just 2004. When it came to formatting the date you would either have to
display it as 1 Jan 2004, or you would have to just display the year for all
the dates in the database.

So, for table storage purposes you are probably best leaving things as they
are.

However, for sorting then you do need to convert your text into real dates
in some way. You will have to decide how you want to sort a value of 2004,
or Apr-2004. Realistically you only have two options - treat them as if they
were on the first or last of the year/ month.

The conversion of your existing text format date should be done in a query,
leaving the original text in place in the table. This will allow you to sort
by the converted value, but display the original text so you don't then have
any problems with formating the dates in reports etc. The big problem, of
course, is how you convert the text into a date. I suspect you will have to
write a VBA function to do this. How easy this is will depend on how
consistent the date entries are in your table.

If they are all either; a recognisable date (eg 1-Apr-2004, or 1/4/04), or a
year (eg 2004), or a month and year in the format Apr-2004, then it should
be fairly simple. The following code should work in this case - it will
return the first of the year/ month for partial dates, the full date for
complete dates or 1 Jan 1900 for unrecognised dates. This is a simple
example, and could be extended to recognise more options if your data is
less consistent than this.

Public Function TextToDate(strDate As String) As Date

Dim strYear As String
Dim strMonth As String

If IsDate(strDate) Then
    TextToDate = strDate
ElseIf IsNumeric(Right(strDate, 4)) Then
    'Last 4 digits are numeric, so assume it is the year
    If Len(strDate) = 4 Then
        TextToDate = DateSerial(CLng(strDate), 1, 1)
    ElseIf Len(strDate) = 8 Then
        strYear = CLng(Right(strDate, 4))
        strMonth = Left(strDate, 3)
        TextToDate = CDate("01-" & strMonth & "-" & strYear)
    Else
        'Unrecognised format - treat all these cases as 1 Jan 1900
        TextToDate = DateSerial(1900, 1, 1)
    End If
Else
    'Unrecognised format - treat all these cases as 1 Jan 1900
    TextToDate = DateSerial(1900, 1, 1)
End If

End Function

"harvestman" <anonymous@discussions.microsoft.com> wrote in message
news:195b101c41c7b$3e7ea9a0$a401280a@phx.gbl...
> I have a quite large Access db with about 6,000 records,
> each with 32 fields divided into three tables. I'm having
> a problem with dates in one of my tables, especially since
> I'm trying to get the dates listed in chronological order.
>
> My db relates to my Uni thesis here in Oz, recording
> occurrences of invertebrate species with dates running
> from 1883 to 2004. I have reconfigured my short date under
> Regional Settings Properties to read as: dd-mmm-yyyy
> (e.g., 07-Apr-2004). However, the problem I have is the
> fact that for about a quarter of my records I only have
> a "mmm-yyyy" date or "yyyy" date and have no date
> information at all for about a third of my records.
>
> If I select "Date/Time" as my data type in the field I
> have shown for the Collection Date, I lose all my "mmm-
> yyyy" and "yyyy" dates. Similarly, under the General Tab
> for Field Properties, in the first property: "Format", I
> can only select a single (particular) date style
> e.g., "short date", rather than the three date style
> values I want to select... i.e.,
> dd-mmm-yyyy e.g., 07-Apr-2004
> mmm-yyyy e.g., Apr-2004
> yyyy e.g., 2004
>
> So if anyone can advise me here, it would be much
> appreciated... and in the instances where I have no date
> info, can someone please advise if I should leave the
> value for this field blank, or write something like "null"
> or "unknown".
>
> Many thanks...

• Previous message: Andrew Smith: "Re: converting record info to fields"
• In reply to: harvestman: "Multiple date formats in a Table"
• Next in thread: John Nurick: "Re: Multiple date formats in a Table"
• Messages sorted by: [ date ] [ thread ]