Counting guests based upon their ages

[bellevue]

I have a database that records details of guests. I have three age categories (each charged at different rates) and I have a field on the form for the total of each age group in that booking (see attached):

Age Group Field



0-12 Years Qty 0-12
13-17 Years Qty 13-17
18 Years and over Qty Adults

I'm struggling to come up with an expression that will (based upon the ages entered in "Age 1", "Age 2" etc.) total the number of each age group in "Qty 0-12", "Qty 13-17" and "Qty Adults".

I've tried the following but it doesn't work:

=IIf([Age 1] >=18,1,IIf([Age 2] >=18,+1,IIf([Age 3] >=18,+1,IIf([Age 4] >=18,+1,IIf([Age 5] >=18,+1,IIf([Age 6] >=18,+1, 0))))))



Can anyone help please ?

[orange]

I don't follow your Age 1 Age 2 Age 3...

The field you should be checking, as I understand your post, is Age.

The iif statement to see to which group a person is assigned is

IIf(age < 13, 1, (IIf(age < 18, 2, 3)))



You could use a small proc to get the counts along these lines:
Run it in step mode and adjust the age; do a cntl F9 to reset the active line using different dates

Sub testiif()

'to get 3 ageGroups 0-12, 13-17, 18+
Dim age As Integer
Dim agegroup(3) As Integer ' i realize this reserves positions 0-3 but we'll use just 1, 2 and 3
Dim i As Integer
age = 6 ' change this 14, 19, 23, as you wish
Debug.Print IIf(age < 13, 1, (IIf(age < 18, 2, 3)))
i = IIf(age < 13, 1, (IIf(age < 18, 2, 3)))
agegroup(i) = agegroup(i) + 1
Debug.Print "agegroup(" & i & ") "; agegroup(i)
End Sub

[bellevue]

Thanks for that

To explain the Age 1, Age 2...thing:

The field "Age 1" shows the age of Guest 1
The field "Age 2" shows the age of Guest 2

..and so on.

There is no field called "Age"; the iif statement needs be set in the fields "Qty 0-12", "Qty 13-17" and "Qty Adults" so that when it runs (say for a family of 4 with ages of 42, 40, 17, 11) it would return the following results:

"Qty 0-12" - 1
"Qty 13-17" - 1
"Qty Adults" - 2

Thanks for the "proc" but I don't understand that, what it is, what it does or where it goes and would prefer an iif statement if at all possible.

Best wishes

[orange]

A couple of things:

You show Age1 to Age6, what are you going to do if you get 7 or 10 people?
I think you have a table strucutre issue, but you should post your database relationships and table layouts so we can see what you have.

As for the code/procedure, it is a small piece of vba.

You should do a little reading on procedures.

You can copy the code I gave,
hit cntl-G to open your database at the appropriate spot,
open a new module
paste the code

Put your cusor in the procedure code anywhere
hit F8, and the code will begin executing in step mode
each time you hit F8, the next logical line will execute

If the immediate window is open (and it should be) you will see output printed in the immediate window.

When your step to the Debug.Print line, you can put your cusor on the Age= 6 line, hit F9 to reposition your next exectue line there
change the value from 6 to 18 or whatever and continue with F8

You will see how the debug.print will print out the results.

This is a very brief intro to vba .

You should really go to http://www.functionx.com/vbaccess/