Solved Insert into statement from unbound text box to a table. (1 Viewer)

[cheekybuddha]

Wow! I really must need some more coffee today!

I missed out a comma after the 't.*'

See if this works:

SELECT
  t.*,
  Switch(
    t.Age < 4, '0 - 3',
    t.Age < 12, '4 - 12',
    t.Age < 13, '12 - 13',
    t.Age < 17, '13 - 17',
    t.Age < 30, '18 - 30',
    t.Age >= 30, '30 plus'
  ) AS [Range]
FROM (
  SELECT
    ID,
    [Name],
    DoB,
    IIf(
      IsDate([DoB]),
      Year(Date()) - Year([DoB]) + (Date() < DateSerial(Year(Date()), Month([DoB]), Day([DoB]))),
      Null
    ) AS Age
  FROM tblReg
) t;

 

[saledo2000]

Note that the OP and Arnel's code assumes everyone's birthday anniversary is on January 1. Use the DoB formula in #18 for an accurate value of age.

Thank you for your advice.
Could you please suggest since I am a beginner in programming.

 

[MajP]

I like to do this using a small table because you never know when you are going to want to change the groups or add more groups

tblRanges

ID    RangeGreaterOrEqual    RangeLessThan    RangeName
1    0     4          0-3
2    4     13          4-12
3    13     18          13-17
4    18     31          18-30
5    30     9999999  >30

[/code]
Using the function

Public Function AgeYears(ByVal datBirthDate As Date) As Integer
' Comments: Returns the age in years
' Params : datBirthDate Date to check
' Returns : Number of years
' Source : Total Visual SourceBook
On Error GoTo PROC_ERR
Dim intYears As Integer
intYears = Year(Now) - Year(datBirthDate)
If DateSerial(Year(Now), Month(datBirthDate), Day(datBirthDate)) > Now Then
' Subtract a year if birthday hasn't arrived this year
intYears = intYears - 1
End If
AgeYears = intYears
PROC_EXIT:
Exit Function
PROC_ERR:
MsgBox "Error: " & Err.Number & ". " & Err.Description, , "modDateTime.AgeYears"
Resume PROC_EXIT
End Function

Probably can do it in one query with a sub, but kind of a pain
qryAgeInYears

SELECT tblPerson.PersonID,
tblPerson.BirthDate,
ageyears([birthdate]) AS AgeYears
FROM
tblPerson;

Then

SELECT
qryAgeInYears.PersonID,
qryAgeInYears.BirthDate,
qryAgeInYears.AgeYears,
tblRanges.RangeName
FROM
qryAgeInYears, tblRanges
WHERE
  (((qryAgeInYears.AgeYears)>=[RangeGreaterOrEqual] And (qryAgeInYears.AgeYears)<[RangeLessThan]));

 

[saledo2000]

Wow! I really must need some more coffee today!

I missed out a comma after the 't.*'

See if this works:

SELECT
  t.*,
  Switch(
    t.Age < 4, '0 - 3',
    t.Age < 12, '4 - 12',
    t.Age < 13, '12 - 13',
    t.Age < 17, '13 - 17',
    t.Age < 30, '18 - 30',
    t.Age >= 30, '30 plus'
  ) AS [Range]
FROM (
  SELECT
    ID,
    [Name],
    DoB,
    IIf(
      IsDate([DoB]),
      Year(Date()) - Year([DoB]) + (Date() < DateSerial(Year(Date()), Month([DoB]), Day([DoB]))),
      Null
    ) AS Age
  FROM tblReg
) t;

It is working like a charm. Thank you very much. You have put really excellent effort to lead me all the way. I will try now to count total number of IDs for every Age range group.
Thanks again and hope to hear you again in the future.

 

[saledo2000]

I like to do this using a small table because you never know when you are going to want to change the groups or add more groups

tblRanges

ID    RangeGreaterOrEqual    RangeLessThan    RangeName
1    0     4          0-3
2    4     13          4-12
3    13     18          13-17
4    18     31          18-30
5    30     9999999  >30

[/code]
Using the function

Public Function AgeYears(ByVal datBirthDate As Date) As Integer
' Comments: Returns the age in years
' Params : datBirthDate Date to check
' Returns : Number of years
' Source : Total Visual SourceBook
On Error GoTo PROC_ERR
Dim intYears As Integer
intYears = Year(Now) - Year(datBirthDate)
If DateSerial(Year(Now), Month(datBirthDate), Day(datBirthDate)) > Now Then
' Subtract a year if birthday hasn't arrived this year
intYears = intYears - 1
End If
AgeYears = intYears
PROC_EXIT:
Exit Function
PROC_ERR:
MsgBox "Error: " & Err.Number & ". " & Err.Description, , "modDateTime.AgeYears"
Resume PROC_EXIT
End Function

Probably can do it in one query with a sub, but kind of a pain
qryAgeInYears

SELECT tblPerson.PersonID,
tblPerson.BirthDate,
ageyears([birthdate]) AS AgeYears
FROM
tblPerson;

Then

SELECT
qryAgeInYears.PersonID,
qryAgeInYears.BirthDate,
qryAgeInYears.AgeYears,
tblRanges.RangeName
FROM
qryAgeInYears, tblRanges
WHERE
  (((qryAgeInYears.AgeYears)>=[RangeGreaterOrEqual] And (qryAgeInYears.AgeYears)<[RangeLessThan]));

Thank you a lot MajP. Will certainly try your way and will post back the result. You are excellent in teaching us beginners and hope to learn more advises from you. Thanks again.

 

[cheekybuddha]

>> I will try now to count total number of IDs for every Age range group. <<

You can use an aggregate query around the query you used. Shout if you get stuck.

Or you can use Arnel's suggestion, but it is more difficult to get the Ranges you want.

MajP's method also works well.



d