Linking two fields from two different tables

[um4r12]

Hi, I have two different tables. Table1 contains information such as the name of a patient, which is the primary key, date of birth etc (fields that will not change). Table2, linked by the subject id, contains information about tests that they may have gone through. A form has been made using these two tables, in which table1 is that main form, and table2 is a subform. I want to add a field in table2 called "CurrentAge", which will use the date of birth specified in table1, to calculate their current age, and would like it to be displayed in the subform. How would I be able to do that? Thanks!

[June7]

Names can be poor unique identifiers. What if you have multiple John Smith? If patient name is the primary key, how can these tables be 'linked by the subject id'? Why would you need to show the calculated age in subform?

[um4r12]

We use the first three letters of their last name + the first letter of their first name. So for John Smith, it becomes SMIJ. It will be easier to work with, as their current age is required to do a few statistical tests on them. Otherwise, we would have to manually calculate their current age.

[June7]

Same issue, what if you have more than one patient with same name?

Doesn't address the question about linking on subject ID - is that the patient ID SMIJ?

An expression in subform control can reference field or control in main form. Can calculate the age on main form then reference that control. Still, how is the subform going to use the calculated age?

[um4r12]

Yes, the subject ID would be SMIJ. I'm not sure if I understood the last question. All we want to do is display the current age, it is not going to be used elsewhere in the form.

[June7]

If all you want to do is display the age and the date of birth is on the main form, then why do you need the age in the subform? - calculate on main form. Should not store age in table. Do you want to know the age at time of test? Then yes, do a calc in subform that references the main form date of birth field.

Use DateDiff() function.

I still think the constructed key could be an issue.

[um4r12]

Yes, thats exactly right, we want to know the age at the time of test. I tried referencing from the main form, but the problem I think that was occurring was that the main form uses a different data format, which we do not want to change(ex. 27-Sept-1995), and when using that function, it requires it to be in short date format. Is that right? If so, how can I work around that?

[June7]

If the dates are in a date/time type field, the format property settings are irrelevant. If the date is in a text type field, that does complicate. Will require calc to convert the text string to actual date value.

The calc for age at time of test must be in the subform.

[um4r12]

That makes sense. I got it to work, using the function that you had mentioned, however, I had to change the date formatting. But how would I convert the text string(It is masked) to the actual date value so that I do not have to make changes to the date formatting. Thanks for all the help!

[June7]

Often requires string manipulation functions (Left, Mid, InStr) to break up the string and DateSerial to recombine as a date value. However, in your case, try CDate() function.

Why are you saving date to a text field? I use mask for date/time field.