Need help with Datediff

[rajdeepsokhi]

Hi,
I am trying to calculate the number of days between 'today' and a 'date of birth' field. In the field properties Default Value I am using =(DateDiff('y',Date(),[dob]) where Date() gives today's date and [dob] is my 'date of birth' field. However, this is not working. I get an error "(The database engine does not recognize either the field 'dob' in a validation expression, or the default value in the table 'Table1')"

I have set the data type for both fields to 'Date/Time'. What am I doing wrong? Is there any alternate method?

Also, how can I calculate height in feet & inches (5'6" or 5.6 or 5-6) from a given height in meters?

Thanks
Raj

 

[Dennisk]

that calculation will return an incorrent DOB (I think for DOBs between now and 31st December
here is one that works correctly

Public Function CalcAge(DOB) As Integer
Dim CompareDate As Date
CompareDate = Date
If Not IsNull(DOB) Then
CalcAge = Year(CompareDate) - Year(DOB) + (DateSerial(Year(CompareDate), Month(DOB), Day(DOB)) > CompareDate)
End If
End Function

Paste this function into a code module then create an unbound text box on a form and set the source to =CalAge([DOB])

or use it in a query but do not store age in a table as it may be out of date the following day.

a cm is 2.54 inches so turn cm to inches and divide by 12 using integer math to get feet, then use the modulos (MOD 12) operator to get the remainder of the inches

 

[rajdeepsokhi]

Thanks Dennis

Datediff function is working on the form (without your code, have not tried that yet) but the value is not getting saved in the corresponding field in the Table. That field 'Age' is set to 'Number'. On the form the 'Control Source' has the Datediff function (not 'Age'). Is that why? How can I fix this?

 

[Dennisk]

don't store calculated controls in a table just display on a form.

 

[accessaz]

a cm is NOT 2.54 inches, maybe the other way around....

 

[boblarson]

a cm is NOT 2.54 inches, maybe the other way around....

A cm is .3937 of an inch and one meter is 39.37 inches.