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

cheekybuddha

AWF VIP
Local time
Today, 11:08
Joined
Jul 21, 2014
Messages
419
Wow! I really must need some more coffee today! :coffee:

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.
Local time
Today, 11:08
Joined
Jan 21, 2013
Messages
70
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

You've got your good things, and you've got mine.
Local time
Today, 06:08
Joined
May 21, 2018
Messages
3,160
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.
Local time
Today, 11:08
Joined
Jan 21, 2013
Messages
70
Wow! I really must need some more coffee today! :coffee:

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.
Local time
Today, 11:08
Joined
Jan 21, 2013
Messages
70
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
Local time
Today, 11:08
Joined
Jul 21, 2014
Messages
419
>> 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
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom