Microsoft Access 2010: trying to get the proper syntax for calculating a field to show 3 different ranges of numbers from the data in another field.

The field 1 has a value in it of 47,
This would be an age.
I want to display the value as an age group in another field, field 2.
Like 35-, 36-45, 46-55, 56-65, 66+

Hope this is clear
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Create a 'Lookup table' to define your Age ranges.

The structure should be like this:

RangeID (autonumber/PrimaryKey)
StartAge (Numeric)
EndAge (Numeric)
Description (Text)

Te table populated with data should look something like this:

RangeID      StartAge         EndAge          Description
  1                     0                    35                  Under 35
  2                     36                   45                  36-45
  3                     46                   55                   46-55
  4                     55                  65                56-65
  5                   66                    500                 Over 65

Then rather than having a cacluated field in your table for the age range (this would not be properly normalized), you should lookup the age range to display it on forms and reports on an as-needed basis.  

One way to do this is using DLookup:

Me.TxtAgeRange = DLookup("Description","tblAgeRange". "StartAge <= " & intAge & " AND EndAge >=" & intAge)

The exact syntax would depend on how you are applying it.

Vitor MontalvãoMSSQL Senior EngineerCommented:
You can do it in a single query. Example:
SELECT age, iif(age<=35, '35-', 
                     iif(age<=45, '36-45', 
                     iif(age<=55, '46-55', 
                     iif(age<=65, '56-65', '66+'))))
FROM Table1;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vinv1Author Commented:
VMontalvao: solution is good.

How do I use this in the criteria line  to populate a calculated field?

                           AGE OF PERSON        AGE GROUP OF PERSON (RESULT FROM "AGE OF PERSON")
                                      47                                   46-55

CRITERIA     SELECT age, iif(age<=35, '35-',
                     iif(age<=45, '36-45',
                     iif(age<=55, '46-55',
                     iif(age<=65, '56-65', '66+'))))

will this work??
vinv1Author Commented:
I didn't want to do this as SQL but I finally did it as a Query and it was entered as an Expression in a new field by the system???? Anyway i got the syntax tested.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.