Try the following, which will return the age in days.

SELECT *, CINT(Now()-[Birth Date]) as AGE FROM TableName

For age in years use:

SELECT *, INT((Now()-[Birth Date]) / 365.242199) as AGEYRS from TableName

(note, CINT rounds, INT doesn t)

The reason that this works is that ACCESS stores its date/time in a similar method as Delphi, as a float where the integer portion is the number of days since a specific day and the fractional part as the fractional portion of that day ( 0.25 = 6 am, 0.50 = noon, etc). Thus if you want to know the differences between two days, just take the differences between the day numbers... for number of years, divide this by the number of days in a year.

EDIT

Another option here would be to create a calculated field in Delphi and perform the logic there. In your onCalculated event you would code something like the following:

procedure TForm1.ds1CalcFields(DataSet: TDataSet);
begin
  DataSet.FieldByName( CALCDATE ).AsInteger := 
    Trunc((Date - DataSet.FieldByName( BIRTH DATE ).AsDateTime) / 365.242199);
end;

EDIT

And yet a third method. Rather than allow the refresh to work as it currently does, override the behavior and force a close/reopen of the dataset by using the onClick of the navigator:

procedure TForm1.dbnvgr1Click(Sender: TObject; Button: TNavigateBtn);
begin
  if Button = nbRefresh then
    begin
      ds1.Close;
      ds1.Open;
    end;
end;

Try this interval parameter instead :

SELECT *, (DateDiff ( yyyy ,[Birth Date], Now())) AS [Age] FROM TableName

Here s what "intervals" stands for :

yyyy    Year
q   Quarter
m   Month
y   Day of Year
d   Day
w   Weekday
ww  Week
h   Hour
n   Minute
s   Second

I did a test with your previous comment, and I also have the same error.

I think it s a bug somewhere in the Navigator or in jet.

When you hit refresh on the nav. the 40149 shown is the date representation as a double without the calculated stuff. It seem to use only the first column found and to display it.

If you try to cast it to a string, the displayed data is still a datetime.

Select *,     & DateDiff(.......) as [Age] From table1;

When I use a column of type String or Number first in the calculated field, the result is displayed as it should. You can try :

SELECT *,  mid(id & (DateDiff ( y ,[madate], Now())), len(id) + 1) AS [Age] FROM Table1

Or :

SELECT *,  (id-id) + (DateDiff ( y ,[madate], Now()))  AS [Age] FROM Table1

This is pretty ugly, but it does the trick..