Using a date from a specific table record to calculate age (1 Viewer)

I have SQL Server backend and Access frontend. Three of my tables are Contacts, Settings, and Tournaments. Contacts is pretty obvious and includes a DOB field. Settings is used for setting default values for specific functions including identifying the current tournament. Tournaments list all the tournaments and details, including the tournament date.

In SQL server I have a scalar function for calculating date difference in years. I use it in the Contacts table for a calculated field for Age based on the current date - ([dbo].[GetDateDiffY]([DOB],getdate())). Works as I want it to.

I also need to calculate the contact's age, in years, on the date of the tournament. I need to be able to have SQL server identify the current tournament set in Settings and get the current tournament's date from the Tournaments table. I can create a calculated field using something similar to - ([dbo].[GetDateDiffY]([DOB],tournamentDate())). I need your assistance to define tournamentDate() in SQL server.

I know I can do this in Access. I have functions in Access called GetDefTournament() and GetDefTournamentDate(). I want to do this in SQL and let SQL do the work.

Any suggestions/assistance are appreciated.

What does the Access function look like (it will show how the data needs to be pulled out)?

That's what I'm trying to figure out. In Access its just a simple dlookup for GetDefTournament() and GetDefaultTournamentDate(). I want to be able to put a function in SQL Server called GetTournamentDate() that returns the correct tournament date from the Tournaments table. Basically that works like getdate(), which returns the current date, but instead returns the current tournament date.

With or without a criteria? Here's a function that takes a couple of parameters to return a value from a settings table:

cross posted here https://www.accessforums.net/showthread.php?t=78038&p=437719#post437719

Thanks pbaldy you pointed me in the right direction. Thinking about it, just like in Access, with VBA, I still need two functions. One to identify the current tournament in the Settings table, because there are other uses for it, and one to get the current tournament date from the Tournaments table.

If the settings table will only have a single record, you could avoid the first function and just include the settings table in the second function's SQL and using the date field in the criteria.

Could you give me an example of how I would include that in the second function, please?

Try

... FROM Tournaments, Settings WHERE Tournaments.TID = Settings.TID

Thank you.

No problem. Normally not having a join between the tables would cause a problem (Cartesian product) but as long as there's only one record in Settings that should work fine.