Rounding down in ACCESS

Question

Problem: Rounding down dates

Situation: I'm working on a medieval history database. One of the data fields is [year], masked for four digits. I want to calculate and express this year as a century, which is easy to do in Excel with the following:

=ROUNDDOWN(([year]/100),0)+1

This equation guarantees that both "1901" and "1999" would show up as "20" (meaning 20th century) in the [century] field. In order for both of these dates to appear, however, as 20, I need to force the ROUNDDOWN after the division by 100. I could, alternatively, force a ROUNDUP, and then remove the +1 at the end. However, I don't know how to do that, either, in Access.

Thoughts?

Thank you

Answer 1

Assuming you don't need to handle BCE dates, simply use integer division by 100 and add 1, e.g. in the immediate window:

? 1485\100+1

 15

? 1346\100+1

 14

? 1066\100+1

 11

? 735\100+1

 8

Answer 2

ciao Joshua,

what about this :

SELECT Tabella1.anno AS myYear, IIf([anno]<0,"AC ") & Abs(Fix([anno]/100))+1 AS century

FROM Tabella1;

Ciao, Sandro.