Pass a calculated value in a query?

[moorej2]

I have a calculated value, AGE based on the following standard formula;

Age:
DateDiff("yyyy",[birthdt],Now())+Int(Format(Now(),"mmdd")<Format([birthdt],"mmdd"))

I am currently selecting this in a query, but what I would really like
is the AGE_RANGE value from another table, tblAgeBands, which takes the
following format;

age_min
age_max
age_band (PK)

Unfortunately, I don't know how to write the SQL or form the query so
that the corresponding AGE_BAND value is selected where AGE_MIN < AGE >
AGE_MAX or if it's even possible.

Any help appreciated.

Jon

 

[Jeff L]

Include tblAgeBands as part of your query, output age_band, and in the
criteria of Age put
Between [age_min] And [age_max]

Hope that helps!

 

[Marshall Barton]

I have a calculated value, AGE based on the following standard formula;

Age:
DateDiff("yyyy",[birthdt],Now())+Int(Format(Now(),"mmdd")<Format([birthdt],"mmdd"))

I am currently selecting this in a query, but what I would really like
is the AGE_RANGE value from another table, tblAgeBands, which takes the
following format;

age_min
age_max
age_band (PK)

Unfortunately, I don't know how to write the SQL or form the query so
that the corresponding AGE_BAND value is selected where AGE_MIN < AGE >
AGE_MAX or if it's even possible.

SELECT T.birthdt,
DateDiff("yyyy", T.birthdt, Date()) +
(Format(birthdt,"mmdd") > Format(Date(),"mmdd"))
As Age
B.Age_Band
FROM thetable As T INNER JOIN tblAgeBands As B
ON DateDiff("yyyy", T.birthdt, Date()) +
(Format(birthdt,"mmdd") > Format(Date(),"mmdd")) >=
B.Age_Min
AND DateDiff("yyyy", T.birthdt, Date()) +
(Format(birthdt,"mmdd") > Format(Date(),"mmdd")) <=
B.Age_Max

 

[moorej2]

Include tblAgeBands as part of your query, output age_band, and in the
criteria of Age put
Between [age_min] And [age_max]

Hope that helps!

Perfect Jeff! Thanks very much.