Solved Insert into statement from unbound text box to a table.

[saledo2000]

Hi experts,
I have table tblReg and form frmReg. On that form have an unbound text box (txtAge) for Age where I put expression ***=DateDiff("yyyy", [DoB], Date())*** to count how old is every person in Reg table. Field DoB is bounded to text box txtDoB on the same form. It is working perfectly, since I do not want to update Age after some years. Is there any VBA solution to insert Age value in the tblReg table (field Age) to use it for statistical queries such is age range. I would like to have this INSERT INTO statement for the current record on the Exit button on frmReg form. Until now I wrote a code like:
***currentdb.Execute "INSERT INTO tblReg(Age) VALUES ('" & Me.txtAge & "')" ***
But it does not insert Age in tblReg table.
Could you please help on this matter because I am a new in access world.

Thank you very much.

 

[cheekybuddha]

Why try and store the calculated age when you have the DOB already?

Just use the calculation in any query where you want to perform statistical analysis by age.

 

[saledo2000]

Why try and store the calculated age when you have the DOB already?

Just use the calculation in any query where you want to perform statistical analysis by age.

Thank you for the quick reply.
Yes you are right, but I am not that expert in access. It is easier for me to create query from the table. In my case I need two queries where one will calculate every child (e.g. O for the child old less than one year, 1 for the child old 1 year etc.. al the way to 18 years old). The other query is age range (e.g. 0-3, 4-12, 13-17, 18-30 and 30>).

Cheers.

 

[cheekybuddha]

You can do this in a query:

SELECT
  t.*
  Switch(
    t.Age < 3, '0 - 2',
    t.Age < 6, '3 - 5',
    t.Age < 9, '6 - 9',
    t,Age >= 9, '9 or older'
  ) AS [Range]
FROM (
  SELECT
    ID,
    ChildName,
    DOB,
    IIf(
      IsDate([DOB]), 
      DateDiff("yyyy", [DOB], Date()) + (Date() < DateSerial(Year(Date()), Month([DOB]), Day([DOB]))), 
      Null
    ) Age
  FROM tblReg
) t

Adjust as required.

hth,

d

 

[arnelgp]

the first query is straightforward:
sample (query1)

select ID, Year(Date()) - Year([DOB]) As Age from yourTableName

the second query can be achieved by using Total query and the use of Partition function (using query1 above):

SELECT Partition([Age],DMin("Age","query1"),DMax("Age","query1"),4) AS Range, Count(query1.ID) AS CountOfID
FROM query1
GROUP BY Partition([Age],DMin("Age","query1"),DMax("Age","query1"), 4);

Result:

Range    CountOfID
 0: 3    3
 4: 7    4
 8:11    4
12:15    4
16:19    4
20:23    4
24:26    3

 

[saledo2000]

You can do this in a query:

SELECT
  t.*
  Switch(
    t.Age < 3, '0 - 2',
    t.Age < 6, '3 - 5',
    t.Age < 9, '6 - 9',
    t,Age >= 9, '9 or older'
  ) AS [Range]
FROM (
  SELECT
    ID,
    ChildName,
    DOB,
    IIf(
      IsDate([DOB]),
      DateDiff("yyyy", [DOB], Date()) + (Date() < DateSerial(Year(Date()), Month([DOB]), Day([DOB]))),
      Null
    ) Age
  FROM tblReg
) t

Adjust as required.

hth,

d

Thank you for your help.
I am getting an error in FROM tblReg part when I run the query

 

[cheekybuddha]

Did you substitute your actual field names in to the query?

 

[MajP]

I think your table is called just reg not tblreg

 

[saledo2000]

I think your table is called just reg not tblreg

Yes I did substitute all fields from my table. And my table name is tblReg. Just to inform my date format is Short date dd.mm.yyyy.
By the way I am creating queries in MS Access.

 

[MajP]

OK I was looking at your Insert where you just used Reg.

 

[saledo2000]

OK I was looking at your Insert where you just used Reg.

Thank you MajP. It was typing error in this thread from my side. I am still looking for the solution.

 

[cheekybuddha]

You should change the field called 'Name', otherwise you must surround it with square brackets as it's a reserved word in Access.

SELECT
  t.*
  Switch(
    t.Age < 3, '0 - 2',
    t.Age < 6, '3 - 5',
    t.Age < 9, '6 - 9',
    t.Age >= 9, '9 or older'
  ) AS [Range]
FROM (
  SELECT
    ID,
    [Name],
    DoB,
    IIf(
      IsDate([DoB]),
      DateDiff("yyyy", [DoB], Date()) + (Date() < DateSerial(Year(Date()), Month([DoB]), Day([DoB]))),
      Null
    ) AS Age
  FROM tblReg
) t

I added an 'AS' to the alias for 'Age', and also corrected a typo (switched a comma to a dot in 't,Age')

 

[saledo2000]

You should change the field called 'Name', otherwise you must surround it with square brackets as it's a reserved word in Access.

SELECT
  t.*
  Switch(
    t.Age < 3, '0 - 2',
    t.Age < 6, '3 - 5',
    t.Age < 9, '6 - 9',
    t.Age >= 9, '9 or older'
  ) AS [Range]
FROM (
  SELECT
    ID,
    [Name],
    DoB,
    IIf(
      IsDate([DoB]),
      DateDiff("yyyy", [DoB], Date()) + (Date() < DateSerial(Year(Date()), Month([DoB]), Day([DoB]))),
      Null
    ) AS Age
  FROM tblReg
) t

I added an 'AS' to the alias for 'Age', and also corrected a typo (switched a comma to a dot in 't,Age')

I did remove it and still getting the same error. It is like cannot select from my tblReg. FROM is highlighted every time I run the query. Corrected all typos and getting a new error. Error is highlighted on asterisk *

 

[cheekybuddha]

OK, let's try in stages. First just try:

  SELECT
    ID,
    [Name],
    DoB,
    IIf(
      IsDate([DoB]),
      DateDiff("yyyy", [DoB], Date()) + (Date() < DateSerial(Year(Date()), Month([DoB]), Day([DoB]))),
      Null
    ) AS Age
  FROM tblReg

See if that works by itself.

 

[saledo2000]

OK, let's try in stages. First just try:

  SELECT
    ID,
    [Name],
    DoB,
    IIf(
      IsDate([DoB]),
      DateDiff("yyyy", [DoB], Date()) + (Date() < DateSerial(Year(Date()), Month([DoB]), Day([DoB]))),
      Null
    ) AS Age
  FROM tblReg

See if that works by itself.

OK, let's try in stages. First just try:

  SELECT
    ID,
    [Name],
    DoB,
    IIf(
      IsDate([DoB]),
      DateDiff("yyyy", [DoB], Date()) + (Date() < DateSerial(Year(Date()), Month([DoB]), Day([DoB]))),
      Null
    ) AS Age
  FROM tblReg

See if that works by itself.

New error produced on this short query. Now DoB field is highlighted.

 

[cheekybuddha]

OK, let's try it Arnel's way.
Try:

  SELECT
    ID,
    [Name],
    DoB,
    IIf(
      IsDate(DoB), 
      Year(Date()) - Year([DoB]) + (Date() < DateSerial(Year(Date()), Month([DoB]), Day([DoB]))), 
      NULL
    ) AS Age
  FROM tblReg

 

[saledo2000]

OK, let's try it Arnel's way.
Try:

  SELECT
    ID,
    [Name],
    DoB,
    IIf(
      IsDate(DoB),
      Year(Date()) - Year([DoB]) + (Date() < DateSerial(Year(Date()), Month([DoB]), Day([DoB]))),
      NULL
    ) AS Age
  FROM tblReg

Excellent it is working this Arnel's method. Thank you very much. Now can we try to find age range.

 

[cheekybuddha]

So, now you can incorporate it in to the larger query:

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;

hth,

d

 

[saledo2000]

So, now you can incorporate it in to the larger query:

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;

hth,

d

Problem persists in syntax where asterisk * is highlighted.

 

[Cronk]

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.