BC/AD date handling

[Guest_imported]

I am desinging a database in which I wish to input the dates of item e.g. 100 BC; 75 AD, and then wish to run query on those dates - e.g. find everything between 100BC and 100AD.

Whay do you suggest is the best way to handle BC and AD dates which do not flow sequentially.

Thanks for any help - perhaps the sloution is obvious, or some code for conversion in needed?

 

[DrSimon]

I assume that you only want to use the years rather than full days, months & years. If you do, that would be a problem as any year less than 100AD will not work at all.

Why don't you just have BC years as negative integers and AD as positive? You can then use a between clause in the query

Simon Rouse

 

[LittleSmudge]

To build on Simons idea - you don't have to worry about 'persentation' of negative number because you can get access to display the numbers appropriatly on the form or report. All you are talking about here is STORAGE and MANIPULATION of the underlying data.


G LS

 

[Guest_imported]

Thanks for the advice on this - I have been away from my computer for a day and am slow in replying.

As ever the solution does seem simple: negative and postive integers, with the report side supplying the AD/BC. A friend suggested converting to Julian dates and then back again after the calculation, but that did seem like using a sledgehammer to crack a nut.

I will soemtimes have days/months, but the queries will not be built on these (only years) so that is not a problem.

Thanks

Jim

 

[MichaelRed]

There are several issues in dealing with 'ancient' dates, many of which are dependent on which calendar was used / in use at the time. The use of Julian Dates would resolve many of these, but not the actual display in any MS intrinsic format. From parts of the discussion it appears (to me) that this specific application is basically for use in an 'ancient history' process where the dates are all (ass-u-me-d) to have been properly converted (or their conversion is irrelevant).

If the above interpertation is correct, I woould suggest just adding a field (offset) to the table of the dates. Make all references to the dates through a (small) set of UDFs to manipulate the dates with the offsets. One such example is shown:

Public Function basPreMsDate(MsDate As Date, Optional Offset As Long) As String

    'Michael Red    8/30/2002
    'To 'Display' Dates before 100AD in a date type format

    'Example Usage
    '? basPreMsDate(#1/1/100#, 200)
    '1/100/1BC

    '? basPreMsDate(#1/1/100#)
    '1/100/1AD

    Dim strDt As String

    strDt = Month(MsDate) & "/" & Abs(Year(MsDate) - Offset) & "/" & Day(MsDate)
    If (Year(MsDate) - Offset < 0) Then
        strDt = strDt & &quot;BC&quot;
     Else
        strDt = strDt & &quot;AD&quot;
    End If

    basPreMsDate = strDt

End Function

Note that the routine(s) -with the optional argument- can easily default to the normal display of 'modern' dates, with a fairly small overhead.

Such a schema would lend itself to the generation / use of a class / .dll which could be implemented once and used throughout dbs employing 'ancient' history dates.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over