I have a DOB field in table1 and an Age field in table1 as well.

In table2, I have a field that contains a date that respondents answered
specific questionnaires.

For historical purposes, I would like to capture the age of the person at
the time one questionnaire in particular was answered in the Age field of
table1.

I'm not sure how to go about this, especially since I need the questionnaire
date to be for a specific questionnaire, i.e. questionnaireID 05 specifically.

I have searched high and low and can't seem to find anything like this. Any
help would be greatly appreciated!
--
Thanks!

Dee

You haven't really given us much to go on, but to calculate age at a
particular date in time, you'd use

DateDiff("yyyy", [DOB], [SpecificDate]) - _
IIf(Format([SpecificeDate], "mmdd") < Format([DOB], "mmdd"), 1, 0)

In general, you shouldn't store ages. I suppose that since this particular
age will never change, it might be okay to store it instead of the DOB.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"dee" <(E-Mail Removed)> wrote in message
news:9720D132-2C29-4E01-92A4-(E-Mail Removed)...
>I have a DOB field in table1 and an Age field in table1 as well.
>
> In table2, I have a field that contains a date that respondents answered
> specific questionnaires.
>
> For historical purposes, I would like to capture the age of the person at
> the time one questionnaire in particular was answered in the Age field of
> table1.
>
> I'm not sure how to go about this, especially since I need the
> questionnaire
> date to be for a specific questionnaire, i.e. questionnaireID 05
> specifically.
>
> I have searched high and low and can't seem to find anything like this.
> Any
> help would be greatly appreciated!
> --
> Thanks!
>
> Dee

Thanks for your response. I'm sorry, I thought I had provided enough
information.

I guess where I am having difficulty is referencing the Interview Date field
in another table. I'll try to be more clear:

tblParticipants
pptID (autonumber PK)
First
Last
DOB
etc.

tblResponses
responseID (autonumber PK)
pptID (fk)
questionnaireID (fk) (there are several questionnaires)
interviewDate
interviewLanguage
interviewerID (fk)
etc.

I need to determine the Age of the participant as of the (tblResponses)
interviewDate of questionnaireID **5** only, putting the result in the
tblParticipants.

I realize that this is unusual, but for historical purposes only, plus the
fact that the data will be exported to a statistical package that requires
this field, it's really the only choice I have.

Thanks so much for your help - I hope I have provided enough detail.



--
Thanks!

Dee


"Douglas J. Steele" wrote:

> You haven't really given us much to go on, but to calculate age at a
> particular date in time, you'd use
>
> DateDiff("yyyy", [DOB], [SpecificDate]) - _
> IIf(Format([SpecificeDate], "mmdd") < Format([DOB], "mmdd"), 1, 0)
>
> In general, you shouldn't store ages. I suppose that since this particular
> age will never change, it might be okay to store it instead of the DOB.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "dee" <(E-Mail Removed)> wrote in message
> news:9720D132-2C29-4E01-92A4-(E-Mail Removed)...
> >I have a DOB field in table1 and an Age field in table1 as well.
> >
> > In table2, I have a field that contains a date that respondents answered
> > specific questionnaires.
> >
> > For historical purposes, I would like to capture the age of the person at
> > the time one questionnaire in particular was answered in the Age field of
> > table1.
> >
> > I'm not sure how to go about this, especially since I need the
> > questionnaire
> > date to be for a specific questionnaire, i.e. questionnaireID 05
> > specifically.
> >
> > I have searched high and low and can't seem to find anything like this.
> > Any
> > help would be greatly appreciated!
> > --
> > Thanks!
> >
> > Dee
>
>
>

You could create a query along the lines of:

SELECT responseID, pptID, questionnaireID,
interviewDate,
DateDiff("yyyy", tblParticpants.DOB, tblResponses.interviewDate) -
IIf(Format(tblResponses.interviewDate, "mmdd") < Format(tblParticipants.DOB,
"mmdd"), 1, 0) AS Age,
interviewLanguage, interviewerID, ...
FROM tblResponses INNER JOIN tblParticipants
ON tblResponses.pptID = tblParticipants.pptID
WHERE questionnaireID = 5
UNION
SELECT responseID, pptID, questionnaireID,
interviewDate,
Null,
interviewLanguage, interviewerID, ...
FROM tblResponses
WHERE questionnaireID <> 5

What this would do is give you an additional field Age that would only be
populated for Question 5.

You'd then be able to use that query anywhere you would otherwise have used
tblResponses.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"dee" <(E-Mail Removed)> wrote in message
news:F16EB37B-F74B-4128-BAF8-(E-Mail Removed)...
> Thanks for your response. I'm sorry, I thought I had provided enough
> information.
>
> I guess where I am having difficulty is referencing the Interview Date
> field
> in another table. I'll try to be more clear:
>
> tblParticipants
> pptID (autonumber PK)
> First
> Last
> DOB
> etc.
>
> tblResponses
> responseID (autonumber PK)
> pptID (fk)
> questionnaireID (fk) (there are several questionnaires)
> interviewDate
> interviewLanguage
> interviewerID (fk)
> etc.
>
> I need to determine the Age of the participant as of the (tblResponses)
> interviewDate of questionnaireID **5** only, putting the result in the
> tblParticipants.
>
> I realize that this is unusual, but for historical purposes only, plus the
> fact that the data will be exported to a statistical package that requires
> this field, it's really the only choice I have.
>
> Thanks so much for your help - I hope I have provided enough detail.
>
>
>
> --
> Thanks!
>
> Dee
>
>
> "Douglas J. Steele" wrote:
>
>> You haven't really given us much to go on, but to calculate age at a
>> particular date in time, you'd use
>>
>> DateDiff("yyyy", [DOB], [SpecificDate]) - _
>> IIf(Format([SpecificeDate], "mmdd") < Format([DOB], "mmdd"), 1, 0)
>>
>> In general, you shouldn't store ages. I suppose that since this
>> particular
>> age will never change, it might be okay to store it instead of the DOB.
>>
>> --
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no private e-mails, please)
>>
>>
>> "dee" <(E-Mail Removed)> wrote in message
>> news:9720D132-2C29-4E01-92A4-(E-Mail Removed)...
>> >I have a DOB field in table1 and an Age field in table1 as well.
>> >
>> > In table2, I have a field that contains a date that respondents
>> > answered
>> > specific questionnaires.
>> >
>> > For historical purposes, I would like to capture the age of the person
>> > at
>> > the time one questionnaire in particular was answered in the Age field
>> > of
>> > table1.
>> >
>> > I'm not sure how to go about this, especially since I need the
>> > questionnaire
>> > date to be for a specific questionnaire, i.e. questionnaireID 05
>> > specifically.
>> >
>> > I have searched high and low and can't seem to find anything like this.
>> > Any
>> > help would be greatly appreciated!
>> > --
>> > Thanks!
>> >
>> > Dee
>>
>>
>>

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> дÈëÏûÏ¢ÐÂÎÅ:%(E-Mail Removed)...
> You haven't really given us much to go on, but to calculate age at a
> particular date in time, you'd use
>
> DateDiff("yyyy", [DOB], [SpecificDate]) - _
> IIf(Format([SpecificeDate], "mmdd") < Format([DOB], "mmdd"), 1, 0)
>
> In general, you shouldn't store ages. I suppose that since this particular
> age will never change, it might be okay to store it instead of the DOB.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "dee" <(E-Mail Removed)> wrote in message
> news:9720D132-2C29-4E01-92A4-(E-Mail Removed)...
>>I have a DOB field in table1 and an Age field in table1 as well.
>>
>> In table2, I have a field that contains a date that respondents answered
>> specific questionnaires.
>>
>> For historical purposes, I would like to capture the age of the person at
>> the time one questionnaire in particular was answered in the Age field of
>> table1.
>>
>> I'm not sure how to go about this, especially since I need the
>> questionnaire
>> date to be for a specific questionnaire, i.e. questionnaireID 05
>> specifically.
>>
>> I have searched high and low and can't seem to find anything like this.
>> Any
>> help would be greatly appreciated!
>> --
>> Thanks!
>>
>> Dee
>
>

I posted back yesterday, but just checked and see that for some reason it was
never posted.

I seem to be doing something wrong with the code you sent - I can't seem to
get it to work.

The code I have been using generates what I want when I view it in
datasheet, but I can't seem to apply it to a control on my form - the control
just stays blank. Here is the code:

SELECT tbl_participants.ppt_id,
Int(DateDiff("d",[ppt_dob],[completion_date])/365.25) AS Age
FROM tbl_questionnaires INNER JOIN (tbl_participants INNER JOIN
tbl_responses ON tbl_participants.ppt_id = tbl_responses.ppt_id) ON
tbl_questionnaires.qstnaire_id = tbl_responses.qstnaire_id
WHERE (((tbl_responses.qstnaire_id)=2));

Any help is greatly appreciated! Thanks so much.

--
Thanks!

Dee


"Douglas J. Steele" wrote:

> You could create a query along the lines of:
>
> SELECT responseID, pptID, questionnaireID,
> interviewDate,
> DateDiff("yyyy", tblParticpants.DOB, tblResponses.interviewDate) -
> IIf(Format(tblResponses.interviewDate, "mmdd") < Format(tblParticipants.DOB,
> "mmdd"), 1, 0) AS Age,
> interviewLanguage, interviewerID, ...
> FROM tblResponses INNER JOIN tblParticipants
> ON tblResponses.pptID = tblParticipants.pptID
> WHERE questionnaireID = 5
> UNION
> SELECT responseID, pptID, questionnaireID,
> interviewDate,
> Null,
> interviewLanguage, interviewerID, ...
> FROM tblResponses
> WHERE questionnaireID <> 5
>
> What this would do is give you an additional field Age that would only be
> populated for Question 5.
>
> You'd then be able to use that query anywhere you would otherwise have used
> tblResponses.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "dee" <(E-Mail Removed)> wrote in message
> news:F16EB37B-F74B-4128-BAF8-(E-Mail Removed)...
> > Thanks for your response. I'm sorry, I thought I had provided enough
> > information.
> >
> > I guess where I am having difficulty is referencing the Interview Date
> > field
> > in another table. I'll try to be more clear:
> >
> > tblParticipants
> > pptID (autonumber PK)
> > First
> > Last
> > DOB
> > etc.
> >
> > tblResponses
> > responseID (autonumber PK)
> > pptID (fk)
> > questionnaireID (fk) (there are several questionnaires)
> > interviewDate
> > interviewLanguage
> > interviewerID (fk)
> > etc.
> >
> > I need to determine the Age of the participant as of the (tblResponses)
> > interviewDate of questionnaireID **5** only, putting the result in the
> > tblParticipants.
> >
> > I realize that this is unusual, but for historical purposes only, plus the
> > fact that the data will be exported to a statistical package that requires
> > this field, it's really the only choice I have.
> >
> > Thanks so much for your help - I hope I have provided enough detail.
> >
> >
> >
> > --
> > Thanks!
> >
> > Dee
> >
> >
> > "Douglas J. Steele" wrote:
> >
> >> You haven't really given us much to go on, but to calculate age at a
> >> particular date in time, you'd use
> >>
> >> DateDiff("yyyy", [DOB], [SpecificDate]) - _
> >> IIf(Format([SpecificeDate], "mmdd") < Format([DOB], "mmdd"), 1, 0)
> >>
> >> In general, you shouldn't store ages. I suppose that since this
> >> particular
> >> age will never change, it might be okay to store it instead of the DOB.
> >>
> >> --
> >> Doug Steele, Microsoft Access MVP
> >> http://I.Am/DougSteele
> >> (no private e-mails, please)
> >>
> >>
> >> "dee" <(E-Mail Removed)> wrote in message
> >> news:9720D132-2C29-4E01-92A4-(E-Mail Removed)...
> >> >I have a DOB field in table1 and an Age field in table1 as well.
> >> >
> >> > In table2, I have a field that contains a date that respondents
> >> > answered
> >> > specific questionnaires.
> >> >
> >> > For historical purposes, I would like to capture the age of the person
> >> > at
> >> > the time one questionnaire in particular was answered in the Age field
> >> > of
> >> > table1.
> >> >
> >> > I'm not sure how to go about this, especially since I need the
> >> > questionnaire
> >> > date to be for a specific questionnaire, i.e. questionnaireID 05
> >> > specifically.
> >> >
> >> > I have searched high and low and can't seem to find anything like this.
> >> > Any
> >> > help would be greatly appreciated!
> >> > --
> >> > Thanks!
> >> >
> >> > Dee
> >>
> >>
> >>
>
>
>

That approach to calculating age is flawed.

I don't understand what you mean that you "can't seem to apply it to a
control on my form". How are you trying to use it?

For that matter, what's the problem with the code I gave you? "can't seem to
get it to work" doesn't tell me much.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"dee" <(E-Mail Removed)> wrote in message
news:27058765-A66C-4601-BB05-(E-Mail Removed)...
>I posted back yesterday, but just checked and see that for some reason it
>was
> never posted.
>
> I seem to be doing something wrong with the code you sent - I can't seem
> to
> get it to work.
>
> The code I have been using generates what I want when I view it in
> datasheet, but I can't seem to apply it to a control on my form - the
> control
> just stays blank. Here is the code:
>
> SELECT tbl_participants.ppt_id,
> Int(DateDiff("d",[ppt_dob],[completion_date])/365.25) AS Age
> FROM tbl_questionnaires INNER JOIN (tbl_participants INNER JOIN
> tbl_responses ON tbl_participants.ppt_id = tbl_responses.ppt_id) ON
> tbl_questionnaires.qstnaire_id = tbl_responses.qstnaire_id
> WHERE (((tbl_responses.qstnaire_id)=2));
>
> Any help is greatly appreciated! Thanks so much.
>
> --
> Thanks!
>
> Dee
>
>
> "Douglas J. Steele" wrote:
>
>> You could create a query along the lines of:
>>
>> SELECT responseID, pptID, questionnaireID,
>> interviewDate,
>> DateDiff("yyyy", tblParticpants.DOB, tblResponses.interviewDate) -
>> IIf(Format(tblResponses.interviewDate, "mmdd") <
>> Format(tblParticipants.DOB,
>> "mmdd"), 1, 0) AS Age,
>> interviewLanguage, interviewerID, ...
>> FROM tblResponses INNER JOIN tblParticipants
>> ON tblResponses.pptID = tblParticipants.pptID
>> WHERE questionnaireID = 5
>> UNION
>> SELECT responseID, pptID, questionnaireID,
>> interviewDate,
>> Null,
>> interviewLanguage, interviewerID, ...
>> FROM tblResponses
>> WHERE questionnaireID <> 5
>>
>> What this would do is give you an additional field Age that would only be
>> populated for Question 5.
>>
>> You'd then be able to use that query anywhere you would otherwise have
>> used
>> tblResponses.
>>
>> --
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no private e-mails, please)
>>
>>
>> "dee" <(E-Mail Removed)> wrote in message
>> news:F16EB37B-F74B-4128-BAF8-(E-Mail Removed)...
>> > Thanks for your response. I'm sorry, I thought I had provided enough
>> > information.
>> >
>> > I guess where I am having difficulty is referencing the Interview Date
>> > field
>> > in another table. I'll try to be more clear:
>> >
>> > tblParticipants
>> > pptID (autonumber PK)
>> > First
>> > Last
>> > DOB
>> > etc.
>> >
>> > tblResponses
>> > responseID (autonumber PK)
>> > pptID (fk)
>> > questionnaireID (fk) (there are several questionnaires)
>> > interviewDate
>> > interviewLanguage
>> > interviewerID (fk)
>> > etc.
>> >
>> > I need to determine the Age of the participant as of the (tblResponses)
>> > interviewDate of questionnaireID **5** only, putting the result in the
>> > tblParticipants.
>> >
>> > I realize that this is unusual, but for historical purposes only, plus
>> > the
>> > fact that the data will be exported to a statistical package that
>> > requires
>> > this field, it's really the only choice I have.
>> >
>> > Thanks so much for your help - I hope I have provided enough detail.
>> >
>> >
>> >
>> > --
>> > Thanks!
>> >
>> > Dee
>> >
>> >
>> > "Douglas J. Steele" wrote:
>> >
>> >> You haven't really given us much to go on, but to calculate age at a
>> >> particular date in time, you'd use
>> >>
>> >> DateDiff("yyyy", [DOB], [SpecificDate]) - _
>> >> IIf(Format([SpecificeDate], "mmdd") < Format([DOB], "mmdd"), 1, 0)
>> >>
>> >> In general, you shouldn't store ages. I suppose that since this
>> >> particular
>> >> age will never change, it might be okay to store it instead of the
>> >> DOB.
>> >>
>> >> --
>> >> Doug Steele, Microsoft Access MVP
>> >> http://I.Am/DougSteele
>> >> (no private e-mails, please)
>> >>
>> >>
>> >> "dee" <(E-Mail Removed)> wrote in message
>> >> news:9720D132-2C29-4E01-92A4-(E-Mail Removed)...
>> >> >I have a DOB field in table1 and an Age field in table1 as well.
>> >> >
>> >> > In table2, I have a field that contains a date that respondents
>> >> > answered
>> >> > specific questionnaires.
>> >> >
>> >> > For historical purposes, I would like to capture the age of the
>> >> > person
>> >> > at
>> >> > the time one questionnaire in particular was answered in the Age
>> >> > field
>> >> > of
>> >> > table1.
>> >> >
>> >> > I'm not sure how to go about this, especially since I need the
>> >> > questionnaire
>> >> > date to be for a specific questionnaire, i.e. questionnaireID 05
>> >> > specifically.
>> >> >
>> >> > I have searched high and low and can't seem to find anything like
>> >> > this.
>> >> > Any
>> >> > help would be greatly appreciated!
>> >> > --
>> >> > Thanks!
>> >> >
>> >> > Dee
>> >>
>> >>
>> >>
>>
>>
>>

Sorry, I should have explained that. The Age field doesn't contain any values.

Here is the code as per your instructions, with one minor modification. I
kept getting error 3079, so in the SELECT part, identified pp_id as coming
from tbl_responses. Maybe that's the problem?

SELECT response_id, tbl_responses.ppt_id, qstnaire_id, completion_date,
DateDiff("yyyy",[tbl_participants].[ppt_dob],[tbl_responses].[competion_date])-IIf(Format([tbl_responses].[completion_date],"mmdd")<Format([tbl_participants].[ppt_dob],"mmdd"),1,0)
AS Age, interviewer_id, start_time, end_time, interview_lang
FROM tbl_responses INNER JOIN tbl_participants ON tbl_responses.ppt_id =
tbl_participants.ppt_id
WHERE qstnaire_id=2 UNION SELECT response_id, ppt_id, qstnaire_id,
completion_date, Null,interviewer_id, start_time, end_time, interview_lang
FROM tbl_responses WHERE qstnaire_id <> 2;
--
Thanks!

Dee


"Douglas J. Steele" wrote:

> That approach to calculating age is flawed.
>
> I don't understand what you mean that you "can't seem to apply it to a
> control on my form". How are you trying to use it?
>
> For that matter, what's the problem with the code I gave you? "can't seem to
> get it to work" doesn't tell me much.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "dee" <(E-Mail Removed)> wrote in message
> news:27058765-A66C-4601-BB05-(E-Mail Removed)...
> >I posted back yesterday, but just checked and see that for some reason it
> >was
> > never posted.
> >
> > I seem to be doing something wrong with the code you sent - I can't seem
> > to
> > get it to work.
> >
> > The code I have been using generates what I want when I view it in
> > datasheet, but I can't seem to apply it to a control on my form - the
> > control
> > just stays blank. Here is the code:
> >
> > SELECT tbl_participants.ppt_id,
> > Int(DateDiff("d",[ppt_dob],[completion_date])/365.25) AS Age
> > FROM tbl_questionnaires INNER JOIN (tbl_participants INNER JOIN
> > tbl_responses ON tbl_participants.ppt_id = tbl_responses.ppt_id) ON
> > tbl_questionnaires.qstnaire_id = tbl_responses.qstnaire_id
> > WHERE (((tbl_responses.qstnaire_id)=2));
> >
> > Any help is greatly appreciated! Thanks so much.
> >
> > --
> > Thanks!
> >
> > Dee
> >
> >
> > "Douglas J. Steele" wrote:
> >
> >> You could create a query along the lines of:
> >>
> >> SELECT responseID, pptID, questionnaireID,
> >> interviewDate,
> >> DateDiff("yyyy", tblParticpants.DOB, tblResponses.interviewDate) -
> >> IIf(Format(tblResponses.interviewDate, "mmdd") <
> >> Format(tblParticipants.DOB,
> >> "mmdd"), 1, 0) AS Age,
> >> interviewLanguage, interviewerID, ...
> >> FROM tblResponses INNER JOIN tblParticipants
> >> ON tblResponses.pptID = tblParticipants.pptID
> >> WHERE questionnaireID = 5
> >> UNION
> >> SELECT responseID, pptID, questionnaireID,
> >> interviewDate,
> >> Null,
> >> interviewLanguage, interviewerID, ...
> >> FROM tblResponses
> >> WHERE questionnaireID <> 5
> >>
> >> What this would do is give you an additional field Age that would only be
> >> populated for Question 5.
> >>
> >> You'd then be able to use that query anywhere you would otherwise have
> >> used
> >> tblResponses.
> >>
> >> --
> >> Doug Steele, Microsoft Access MVP
> >> http://I.Am/DougSteele
> >> (no private e-mails, please)
> >>
> >>
> >> "dee" <(E-Mail Removed)> wrote in message
> >> news:F16EB37B-F74B-4128-BAF8-(E-Mail Removed)...
> >> > Thanks for your response. I'm sorry, I thought I had provided enough
> >> > information.
> >> >
> >> > I guess where I am having difficulty is referencing the Interview Date
> >> > field
> >> > in another table. I'll try to be more clear:
> >> >
> >> > tblParticipants
> >> > pptID (autonumber PK)
> >> > First
> >> > Last
> >> > DOB
> >> > etc.
> >> >
> >> > tblResponses
> >> > responseID (autonumber PK)
> >> > pptID (fk)
> >> > questionnaireID (fk) (there are several questionnaires)
> >> > interviewDate
> >> > interviewLanguage
> >> > interviewerID (fk)
> >> > etc.
> >> >
> >> > I need to determine the Age of the participant as of the (tblResponses)
> >> > interviewDate of questionnaireID **5** only, putting the result in the
> >> > tblParticipants.
> >> >
> >> > I realize that this is unusual, but for historical purposes only, plus
> >> > the
> >> > fact that the data will be exported to a statistical package that
> >> > requires
> >> > this field, it's really the only choice I have.
> >> >
> >> > Thanks so much for your help - I hope I have provided enough detail.
> >> >
> >> >
> >> >
> >> > --
> >> > Thanks!
> >> >
> >> > Dee
> >> >
> >> >
> >> > "Douglas J. Steele" wrote:
> >> >
> >> >> You haven't really given us much to go on, but to calculate age at a
> >> >> particular date in time, you'd use
> >> >>
> >> >> DateDiff("yyyy", [DOB], [SpecificDate]) - _
> >> >> IIf(Format([SpecificeDate], "mmdd") < Format([DOB], "mmdd"), 1, 0)
> >> >>
> >> >> In general, you shouldn't store ages. I suppose that since this
> >> >> particular
> >> >> age will never change, it might be okay to store it instead of the
> >> >> DOB.
> >> >>
> >> >> --
> >> >> Doug Steele, Microsoft Access MVP
> >> >> http://I.Am/DougSteele
> >> >> (no private e-mails, please)
> >> >>
> >> >>
> >> >> "dee" <(E-Mail Removed)> wrote in message
> >> >> news:9720D132-2C29-4E01-92A4-(E-Mail Removed)...
> >> >> >I have a DOB field in table1 and an Age field in table1 as well.
> >> >> >
> >> >> > In table2, I have a field that contains a date that respondents
> >> >> > answered
> >> >> > specific questionnaires.
> >> >> >
> >> >> > For historical purposes, I would like to capture the age of the
> >> >> > person
> >> >> > at
> >> >> > the time one questionnaire in particular was answered in the Age
> >> >> > field
> >> >> > of
> >> >> > table1.
> >> >> >
> >> >> > I'm not sure how to go about this, especially since I need the
> >> >> > questionnaire
> >> >> > date to be for a specific questionnaire, i.e. questionnaireID 05
> >> >> > specifically.
> >> >> >
> >> >> > I have searched high and low and can't seem to find anything like
> >> >> > this.
> >> >> > Any
> >> >> > help would be greatly appreciated!
> >> >> > --
> >> >> > Thanks!
> >> >> >
> >> >> > Dee
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

Just realized how incredibly stupid I look. Obviously it worked - I had made
a typo.

I'm really sorry - thanks very much for your patience. Now I need to add
this to a control on my form so that the age is displayed when all other info
is being input, linking it via the ppt_id on the form.
--
Thanks!

Dee


"Douglas J. Steele" wrote:

> That approach to calculating age is flawed.
>
> I don't understand what you mean that you "can't seem to apply it to a
> control on my form". How are you trying to use it?
>
> For that matter, what's the problem with the code I gave you? "can't seem to
> get it to work" doesn't tell me much.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "dee" <(E-Mail Removed)> wrote in message
> news:27058765-A66C-4601-BB05-(E-Mail Removed)...
> >I posted back yesterday, but just checked and see that for some reason it
> >was
> > never posted.
> >
> > I seem to be doing something wrong with the code you sent - I can't seem
> > to
> > get it to work.
> >
> > The code I have been using generates what I want when I view it in
> > datasheet, but I can't seem to apply it to a control on my form - the
> > control
> > just stays blank. Here is the code:
> >
> > SELECT tbl_participants.ppt_id,
> > Int(DateDiff("d",[ppt_dob],[completion_date])/365.25) AS Age
> > FROM tbl_questionnaires INNER JOIN (tbl_participants INNER JOIN
> > tbl_responses ON tbl_participants.ppt_id = tbl_responses.ppt_id) ON
> > tbl_questionnaires.qstnaire_id = tbl_responses.qstnaire_id
> > WHERE (((tbl_responses.qstnaire_id)=2));
> >
> > Any help is greatly appreciated! Thanks so much.
> >
> > --
> > Thanks!
> >
> > Dee
> >
> >
> > "Douglas J. Steele" wrote:
> >
> >> You could create a query along the lines of:
> >>
> >> SELECT responseID, pptID, questionnaireID,
> >> interviewDate,
> >> DateDiff("yyyy", tblParticpants.DOB, tblResponses.interviewDate) -
> >> IIf(Format(tblResponses.interviewDate, "mmdd") <
> >> Format(tblParticipants.DOB,
> >> "mmdd"), 1, 0) AS Age,
> >> interviewLanguage, interviewerID, ...
> >> FROM tblResponses INNER JOIN tblParticipants
> >> ON tblResponses.pptID = tblParticipants.pptID
> >> WHERE questionnaireID = 5
> >> UNION
> >> SELECT responseID, pptID, questionnaireID,
> >> interviewDate,
> >> Null,
> >> interviewLanguage, interviewerID, ...
> >> FROM tblResponses
> >> WHERE questionnaireID <> 5
> >>
> >> What this would do is give you an additional field Age that would only be
> >> populated for Question 5.
> >>
> >> You'd then be able to use that query anywhere you would otherwise have
> >> used
> >> tblResponses.
> >>
> >> --
> >> Doug Steele, Microsoft Access MVP
> >> http://I.Am/DougSteele
> >> (no private e-mails, please)
> >>
> >>
> >> "dee" <(E-Mail Removed)> wrote in message
> >> news:F16EB37B-F74B-4128-BAF8-(E-Mail Removed)...
> >> > Thanks for your response. I'm sorry, I thought I had provided enough
> >> > information.
> >> >
> >> > I guess where I am having difficulty is referencing the Interview Date
> >> > field
> >> > in another table. I'll try to be more clear:
> >> >
> >> > tblParticipants
> >> > pptID (autonumber PK)
> >> > First
> >> > Last
> >> > DOB
> >> > etc.
> >> >
> >> > tblResponses
> >> > responseID (autonumber PK)
> >> > pptID (fk)
> >> > questionnaireID (fk) (there are several questionnaires)
> >> > interviewDate
> >> > interviewLanguage
> >> > interviewerID (fk)
> >> > etc.
> >> >
> >> > I need to determine the Age of the participant as of the (tblResponses)
> >> > interviewDate of questionnaireID **5** only, putting the result in the
> >> > tblParticipants.
> >> >
> >> > I realize that this is unusual, but for historical purposes only, plus
> >> > the
> >> > fact that the data will be exported to a statistical package that
> >> > requires
> >> > this field, it's really the only choice I have.
> >> >
> >> > Thanks so much for your help - I hope I have provided enough detail.
> >> >
> >> >
> >> >
> >> > --
> >> > Thanks!
> >> >
> >> > Dee
> >> >
> >> >
> >> > "Douglas J. Steele" wrote:
> >> >
> >> >> You haven't really given us much to go on, but to calculate age at a
> >> >> particular date in time, you'd use
> >> >>
> >> >> DateDiff("yyyy", [DOB], [SpecificDate]) - _
> >> >> IIf(Format([SpecificeDate], "mmdd") < Format([DOB], "mmdd"), 1, 0)
> >> >>
> >> >> In general, you shouldn't store ages. I suppose that since this
> >> >> particular
> >> >> age will never change, it might be okay to store it instead of the
> >> >> DOB.
> >> >>
> >> >> --
> >> >> Doug Steele, Microsoft Access MVP
> >> >> http://I.Am/DougSteele
> >> >> (no private e-mails, please)
> >> >>
> >> >>
> >> >> "dee" <(E-Mail Removed)> wrote in message
> >> >> news:9720D132-2C29-4E01-92A4-(E-Mail Removed)...
> >> >> >I have a DOB field in table1 and an Age field in table1 as well.
> >> >> >
> >> >> > In table2, I have a field that contains a date that respondents
> >> >> > answered
> >> >> > specific questionnaires.
> >> >> >
> >> >> > For historical purposes, I would like to capture the age of the
> >> >> > person
> >> >> > at
> >> >> > the time one questionnaire in particular was answered in the Age
> >> >> > field
> >> >> > of
> >> >> > table1.
> >> >> >
> >> >> > I'm not sure how to go about this, especially since I need the
> >> >> > questionnaire
> >> >> > date to be for a specific questionnaire, i.e. questionnaireID 05
> >> >> > specifically.
> >> >> >
> >> >> > I have searched high and low and can't seem to find anything like
> >> >> > this.
> >> >> > Any
> >> >> > help would be greatly appreciated!
> >> >> > --
> >> >> > Thanks!
> >> >> >
> >> >> > Dee
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>