Re-running same query

[Megacitizen]

I need to create a query that, when a button is clicked, will display data in pop up form (subform) regarding the individual members of various teams we are sending to our Focus cities. Whilst we need to pull in data from numerous tables to ascertain which team the individual is a member of (to cover situations where two or more people with the same name come from different Bases and/or Nations), the form would only display two criteria - Name and Nationality (in form of Country name rather than the descriptive form - USA instead of American etc).

Given the nationality of an individual may be different from the nation his/her Base is located in, is there any code required to ensure the query pulls the correct info regarding each criteria. Eg. I'm travelling to our current focus city myself, next month, I'm British but travelling from an Australian Base. I need to ensure the user sees this correctly. (I won't be the user though, I'm just setting everything up for my successor, as I won't be returning to Oz afterwards.)

Also, I need to make sure the same query is run each time the query button is pressed. I presume it's possible to create the macro and link it to the query.

 

[Megacitizen]

Query above still stands, if you can spot it in my rambling (I'm struggling and I only wrote it yesterday). However, a new issue has been thrown into the equation.

I have been asked to add in an Age field in my subform, which will not be that relevant to the majority of teams and team members, but will be critical for those who are under the age of 18 at the time of their work in our Focus City.

Scanning the boards, I can see that using the DateDiff <> will calculate the difference between 'DOB' and 'now', but I want the 2nd parameter to be the date the team left the city, rather than 'now'. When we review the forms in years to come we don't really want to be seeing 25+ y-o teenagers, rather 15-18 y-o's at the time of their stay.

Would my query include the line:-

Age: DateDiff("yyyy",[Date of Birth],[Date_Dep]())+Int(Format(Date_Dep(),"mmdd")<Format([Date of Birth],"mmdd"))

or would it need to be re-written somehow?

Failing that, would this be a situation where stating an age rather than calculating one would be preferential. I know that normally, stating an age is frowned upon because it has a shelf life.

Any suggestions or advice greatly appreciated.

 

[Pat Hartman]

Here's a function I use. It is pretty much the same as yours.

Public Function CalculateAge(DOB As Date, AsOfDate As Date) As Integer
    Dim WorkDate As Date
    Dim RawAge As Integer
    RawAge = DateDiff("yyyy", DOB, AsOfDate)
    WorkDate = DateSerial(Year(AsOfDate), month(DOB), Day(DOB))
    CalculateAge = RawAge + (AsOfDate < WorkDate) '(AsOfDate < WorkDate) = 0 or -1
End Function

 

[Megacitizen]

Trying to set up a query to calculate age in my subform, the wizard tells me I can use fields from more than one query or table (I actually want to display 5 fields from 3 tables, 3 of the fields in an existing query). However, when I select my fields required, the wizard then tells me I've selected from more than one table and query.

Is it that I can choose tables OR queries and not a mixture of the two?

 

[Pat Hartman]

Access treats queries and tables interchangeably for most purposes including this one. It doesn't matter if the query includes tables or queries or both. Are you getting an error or a warning? What exactly does it say (post a picture)?