Return value if data is between certain ranges

[plannerg]

Hi,

I am battling with this one and Im sure it will only take someone 30 seconds or so!

I have a query that returns people's ages.

I want to write a query / formula to return a 1 against the ages that are between 18 and 23, and a 0 for the ages outside that range.

Can anyone help me!?

Thankyou.

 

[Uncle Gizmo]

If you search this forum for "Calculate Age" or something similar you should be able to find the code to calculate a persons age.

Ignor me I see you already have the age......

Time for bed I recon... it's 1:30 am here!!!!

 

[plannerg]

Hi,

I can calculate the age now worries. I just want to flag if a person has an age between 18 and 23.

 

[jardiamj]

I think what you need is and update Query that will look something like this:

UPDATE tblAges SET tblAges.flag = IIf(([age] Between 18 And 23),1,0);

I'm assuming you table is called tblAges.
I hope it's what you need. Cheers!

 

[raskew]

Here's an example (using Northwind's Employees table) that you can modify to meet your scenario:

SELECT
    EmployeeID
  , FirstName
  , BirthDate
  , DateDiff("yyyy",[BirthDate],Date())+(DateSerial(Year(Date()),Month([BirthDate]),Day([BirthDate]))>Date()) AS Age
  , IIf([age] Between 48 
AND
   53,1,0) AS Eligible
FROM
   Employees;

Because of Employees ages, I used 48 to 53 as the Eligible zone.
To use, just copy the SQL to a new query then change the Table/Field names as appropriate.

HTH - Bob

 

[jardiamj]

My apologize!, what I posted was very clumsy. You don't need to store data that is based in a formula or calculation shame on me. I must be sleepy when I posted that...lol!
Raskew's example of the SELECT query should do it.
Cheers!