# SolvedInsert into statement from unbound text box to a table. (1 Viewer)

#### cheekybuddha

##### AWF VIP
Wow! I really must need some more coffee today!

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

See if this works:
Code:
``````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

##### Registered User.
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.
Could you please suggest since I am a beginner in programming.

#### MajP

##### You've got your good things, and you've got mine.
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

Code:
``````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
Code:
``````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
Code:
``````SELECT tblPerson.PersonID,
tblPerson.BirthDate,
ageyears([birthdate]) AS AgeYears
FROM
tblPerson;``````
Then
Code:
``````SELECT
qryAgeInYears.PersonID,
qryAgeInYears.BirthDate,
qryAgeInYears.AgeYears,
tblRanges.RangeName
FROM
qryAgeInYears, tblRanges
WHERE
(((qryAgeInYears.AgeYears)>=[RangeGreaterOrEqual] And (qryAgeInYears.AgeYears)<[RangeLessThan]));``````

#### saledo2000

##### Registered User.
Wow! I really must need some more coffee today!

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

See if this works:
Code:
``````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

##### Registered User.
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

Code:
``````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
Code:
``````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
Code:
``````SELECT tblPerson.PersonID,
tblPerson.BirthDate,
ageyears([birthdate]) AS AgeYears
FROM
tblPerson;``````
Then
Code:
``````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

##### AWF VIP
>> 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