DateDiff Problem

[Lee Grant]

Hi folks,

I'm really new to Access and have stumbled across a problem. I'm working on
a database that holds information about about a running event. I'm trying
to calculate how old a runner will be on the day of the race (ageonraceday)

The formula I'm trying to work with is:

=Format(DateDiff("d",[Runner Information]![dob],[Event]![dateofevent]),"yy""
years ""m"" months ""d"" days""")

I keep getting the error message #Name?, which I think is Access trying to
tell me that it doesn't like the two variables.

The two variables are:

dob - date of birth of the runner.
dateofevent - date of the race.

As certain runners come back year after year, I want to be able to calculate
the dates using variables and not embedding a static date into the equation.

I'm not too sure where to go next and I wondered if someone may point me in
the correct direction!

Kindest Regards,

Lee

 

[KC-Mass]

Hi Lee

Look at this function on Allen Browns site.
http://allenbrowne.com/func-08.html

Regards

Kevin

 

[Daniel Pineault]

Lee,

Sadly, you cannot use the DateDiff directly to return the desired result.
DateDiff, in this case, simply returns the number of days between the 2
dates. You then need to create a routine to convert that difference back
into Years/Months/Days.

Simply use the fAge() function found at

http://www.devhut.net/index.php?lang=en&id=0000000011#age

It does exactly what you want. To return a value in the format you want,
simply change the last line to:

LTrim(Str(intHold \ 12)) & " years " & LTrim(Str(intHold Mod 12)) & " months
" & LTrim(Str(dayhold)) & " days"
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

 

[BruceM]

Here are some ways to calculate age:
http://www.mvps.org/access/datetime/date0001.htm

Your formula, to the extent it would work at all, is calculating days
between two dates. For a 25-year-old runner that would be in the vicinity
of 9000, but it won't work in any case. If Runner Information and Event are
tables, you do not have a way of selecting specific records. Presumably dob
is a field in each runner's record, and dateofevent is a field in the Events
tables, but which runner, and which event? There is no way to know. On the
other hand, if the fields are in the Record Source for a form you do not
need to specify the table.

It seems you are trying to format the result so that it returns the text
"years", "months", and "days", with each one followed by the appropriate
number. One problem is that DateDiff returns a number. You can format a
number as a date, but you will not get the expected result. Another problem
is that I don't think you can put literal values into the Format function as
you have done. You can do so in the Format property of a text box, but not
in the function, as far as I know.

 

[Lee Grant]

Thanks Guys,

I managed to get the formula to work with your help.

Thank you for the very quick, excellent advice!

Here are some ways to calculate age:
http://www.mvps.org/access/datetime/date0001.htm

Your formula, to the extent it would work at all, is calculating days
between two dates. For a 25-year-old runner that would be in the vicinity
of 9000, but it won't work in any case. If Runner Information and Event
are tables, you do not have a way of selecting specific records.
Presumably dob is a field in each runner's record, and dateofevent is a
field in the Events tables, but which runner, and which event? There is
no way to know. On the other hand, if the fields are in the Record Source
for a form you do not need to specify the table.

It seems you are trying to format the result so that it returns the text
"years", "months", and "days", with each one followed by the appropriate
number. One problem is that DateDiff returns a number. You can format a
number as a date, but you will not get the expected result. Another
problem is that I don't think you can put literal values into the Format
function as you have done. You can do so in the Format property of a text
box, but not in the function, as far as I know.

Hi folks,

I'm really new to Access and have stumbled across a problem. I'm working
on a database that holds information about about a running event. I'm
trying to calculate how old a runner will be on the day of the race
(ageonraceday)

The formula I'm trying to work with is:

=Format(DateDiff("d",[Runner
Information]![dob],[Event]![dateofevent]),"yy"" years ""m"" months ""d""
days""")

I keep getting the error message #Name?, which I think is Access trying
to tell me that it doesn't like the two variables.

The two variables are:

dob - date of birth of the runner.
dateofevent - date of the race.

As certain runners come back year after year, I want to be able to
calculate the dates using variables and not embedding a static date into
the equation.

I'm not too sure where to go next and I wondered if someone may point me
in the correct direction!

Kindest Regards,

Lee