Dcount based on age

[jamierbooth]

Hi all. In a nutshell - I'm using an unbound text box on a form to show how many people are under 16 from the related table.



I'm building a customer management system which will hold various bits about the person and their family make up. I already have an unbound text box showing the total household members by using a VBA Dcount in the On Load event of the form.

Me.txtMembers = DCount("*", "[tblHHmembers]", "[CustID] = " & CustID) + 1     '+1 to add the client on

Can I further this code to then only show the currently under 16's by looking at the DoB from the tblHHmembers ?

It has been suggested to me to use a query. I don't know SQL (still learning vba!) and if I do it with a query, I'd have to use the query builder grid, but don't know where to start with that way either. I just assumed it would be easier in VB?

Cheers!
Jamie.

[ItsMe]

Sometimes when I want to show calculations I will display them in a subform and base the subform on its own query. Using the query builder to create a named query may help. You could create a "Totals" query, perhaps.

[jamierbooth]

Thanks but as I mentioned, I'm not sure where to start with creating a query (either SQL or with the builder) that can work out people under 16 in the first place!

[jamierbooth]

Anyone...?

Hi all. In a nutshell - I'm using an unbound text box on a form to show how many people are under 16 from the related table.

I'm building a customer management system which will hold various bits about the person and their family make up. I already have an unbound text box showing the total household members by using a VBA Dcount in the On Load event of the form.

Me.txtMembers = DCount("*", "[tblHHmembers]", "[CustID] = " & CustID) + 1     '+1 to add the client on

Can I further this code to then only show the currently under 16's by looking at the DoB from the tblHHmembers ?

It has been suggested to me to use a query. I don't know SQL (still learning vba!) and if I do it with a query, I'd have to use the query builder grid, but don't know where to start with that way either. I just assumed it would be easier in VB?

Cheers!
Jamie.

[azhar2006]

Hi Jamie
Use something like this
IIf([FieldName]=16;" ")

[lfpm062010]

To add the Criteria of under 16 to your existing code:
Syntax of Dcount: Dcount ( COUNTFIELD, TABLENAME, CRITERIA )

If [DoB] is just the number (like 16), use this

Me.txtMembers = DDCount("*","[tblHHmembers]","[CustID] = " & [CustID] & " AND [DoB] < 16 ")+1

If [DoB] is the Date field (like MM/DD/YYYY), use this

Me.txtMembers = DCount("*", "[tblHHmembers]", "[CustID] = " & CustID & " AND DateDiff(""yyyy"", [DoB], Now()) < 16 ") + 1

[jamierbooth]

If [DoB] is the Date field (like MM/DD/YYYY), use this

Me.txtMembers = DCount("*", "[tblHHmembers]", "[CustID] = " & CustID & " AND DateDiff(""yyyy"", [DoB], Now()) < 16 ") + 1

This worked perfectly. Thanks a lot!!! (I took the +1 off the end though, as that was only there to include the cust in overall household size).