Calculating Date in Subform 3

[jgarzoli]

Hi guys/girls,

I have a form with a subform. The main form is based on tblContact, and the subform is based on tblMeeting. Linked by Contact ID. One Contact can have many meetings associated with it.

In the main form I have a field that calculates age based on birthdate. Works great. I would also like to be able to create an age field in the subform, based on a meeting date field. So the age calculated, would be the age of the person at that meeting date. The birthdate field is located in the tblContact. And the meeting date field is located in tblMeeting.

I am having trouble getting a formula to calculate the age at the date of the meeting in the subform.

Any suggestions?

Thanks!

 

[TheAceMan1]

Try this ([blue]you![/blue] substitute proper names in [purple]purple[/purple]):

[blue][purple][b]TextBoxName[/b][/purple]=DateDiff("yyyy", Forms![purple][b]MainFormName[/b][/purple]![purple][b]BirthDate[/b][/purple], Me![purple][b]MeetingDate[/b][/purple])+ Int( Format(Me![purple][b]MeetingDate[/b][/purple], "mmdd") < Format( Forms![purple][b]MainFormName[/b][/purple]![purple][b]BirthDate[/b][/purple], "mmdd"))[/blue]

See Ya! . . . . . .

 

[PHV]

How are you TheAceMan1
Hopefully [purple]TextBoxName[/purple] isn't an unbound control in the Detail section of a continuous subform ...

 

[TheAceMan1]

I'm fine [blue]PHV . . .[/blue] hope this post finds you well!

[blue]Hopefully TextBoxName isn't an [purple]unbound control in the Detail section[/purple] of a continuous subform ...[/blue]

[purple]We'll easily find out . . . thats for sure![/purple] ;-)

See Ya! . . . . . .

 

[jgarzoli]

Thanks for the quick responses. What is the 'Me!' piece in that formula? Do I substitute something in there?

Well, here's what I get when I use the above. When I try to enter it, it gives me a "You may have entered operand without an operator" error. This is the formula based on my forms...

=DateDiff("yyyy", Forms!Contact - Pro!DOB, Me!Meeting Date)+ Int( Format(Me!Meeting Date, "mmdd") < Format( Forms!Contact - Pro!DOB, "mmdd"))

Any ideas???

Thanks again

 

[PHV]

If you use names with embedded space you have to use brackets, examples:
Forms![Contact - Pro]!DOB
Me![Meeting Date]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jgarzoli]

Thanks PH,

I noticed above you mentioned something about it being an unbound textbox in the detail section. It is indeed an unbound textbox in the detail section. Is that a bad thing???

 

[PHV]

An unbound control in the Detail section of a continuous form has the SAME value for all records ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ProgramError]

good point pvh!

Program Error
Programmers do it one finger at a time!