reference field in a table from a form

[LGarcia]

Hi all,
I have fields on a form which include Subject ID and Date of Interview.
In my db I have a table that contains Subject ID and Birthdate.
On the form I have an unbound text box that I want to use to calculate an
age: DateofInterview-Birthdate.
My problem is: How would I reference the Birthdate field from my text box?
Hope someone can help.
Thanks,
LGarcia

 

[Emilia Maxim]

Hi all,
I have fields on a form which include Subject ID and Date of Interview.
In my db I have a table that contains Subject ID and Birthdate.
On the form I have an unbound text box that I want to use to calculate an
age: DateofInterview-Birthdate.
My problem is: How would I reference the Birthdate field from my text box?
Hope someone can help.

LGarcia,

take a look to the built in DLookup function. You can retrieve the
value of Birthdate with this. This would look something like:

DLookup("Birthdate","MyTableWithBirthdate","[Subject ID]=" &
Me![Subject ID])

If there's no record with the given Subject ID, the function returns
Null. Please look up also Help item for details.

As for the age it depends what do you want to obtain: only (complete)
years, (complete) months or years +months +days.

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de

 

[Gary Miller]

One way would be to use DLookup to pull the birthdate from
the table, which I assume must not be the table the form is
based on. Not knowing your table name, I will punt...

=DateDiff("y",[DateofInterview],DLookup("Birthdate","Subject
Table","[SubjectID] = " & Me!SubjectID))

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

 

[John Vinson]

Hi all,
I have fields on a form which include Subject ID and Date of Interview.
In my db I have a table that contains Subject ID and Birthdate.
On the form I have an unbound text box that I want to use to calculate an
age: DateofInterview-Birthdate.
My problem is: How would I reference the Birthdate field from my text box?

DLookUp("[Birthdate]", "[tablename]", "[SubjectID] = " & [SubjectID])

will work. Note that subtracting two dates will give a Double Float
number of days - if you want the subject's age in years at the time of
the interview, use an expression like

DateDiff("yyyy", DLookUp("[birthdate]", "[tablename]", "[SubjectID] =
" & [SubjectID]), [Date of Interview]) -
IIF(Format(DLookUp("[birthdate]", "[tablename]", "[SubjectID] = " &
[SubjectID]), "mmdd") > Format([Date of Interview], "mmdd"), 1, 0)

 

[LGarcia]

Mr Miller,
Your assumption is correct. Wow!! I didn't know you could nest a DLookup in
a DateDiff function.

Thanks to all for the replies!

One way would be to use DLookup to pull the birthdate from
the table, which I assume must not be the table the form is
based on. Not knowing your table name, I will punt...

=DateDiff("y",[DateofInterview],DLookup("Birthdate","Subject
Table","[SubjectID] = " & Me!SubjectID))

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

Hi all,
I have fields on a form which include Subject ID and Date of Interview.
In my db I have a table that contains Subject ID and Birthdate.
On the form I have an unbound text box that I want to use to calculate an
age: DateofInterview-Birthdate.
My problem is: How would I reference the Birthdate field from my text box?
Hope someone can help.
Thanks,
LGarcia