Age Groups

[SeanB]

It had worked in the one I gave you.
I deleted the queries and forms I made which I had recreated from the db you gave me because I couldn't get it to list the results like in the last db you kindly provided for me.
When I tried to do it again, well you have the results.

This is the code I used in Age
Age: DateDiff("yyyy",[Date of birth],Now()) I used now and not Date but it did work with that before.

 

[John Big Booty]

I'm not sure what you could have done to precipitate this Personally I'd bin that DB and start again from scratch.

You need to be a little bit careful with Now() as it stores a time component as well as the date. This means that if you try to compare two values collected using the Now() function it is highly unlikely that they will every be equal (=). It will work as an alternate to Date() in this case, however as a general rule I would advise against using Now() unless the time component is specifically required.

 

[SeanB]

I'll try doing just an age groups db from scratch and see if I can get it to work. That db is just a draft, a way for me to learn and to see what they want in it and how it's implimented. I'm sure recreating wont take too long.

 

[Vassago]

If you go into the VBA code, do you see anything with MISSING under Tools-> References? If so, remove the checkbox, but remember what it is, then go back in and check it again. Access corruption can cause this to happen, it's happened to me before.

 

[SeanB]

The only things checked are
visual basic for applications
ms access 11.0 object library
ole automation
ms office 14.0 access db engine objects library

It seems most things installed on my pc are listed in the references.
I do have 4 more visual basic for applications unchecked.

 

[dfenton]

OLE Automation is probably not necessary. If you examine its contents in the Object Browser, you'll see that it offers virtually nothing of any use. I really can't imagine why MS thought it was wise to make it a default reference.

 

[ChrisO]

In post #17 the download is password protected for modules; why not tell us the password?

In any case, if you have defined a Public function in a standard module and that Function is called Date() then you can expect the problem you are having.

Password please…

 

[SeanB]

The password is Social. No need for it i was just trying adding security and forgot to take it off.
My mistake.
It has to be a capital S. Apologies.

 

[ChrisO]

On my machine the project has a missing reference to: -

Microsoft Office 14.0 Access database engine Object Library

When I delete that reference the Date() function is recognised.

 

[dfenton]

On my machine the project has a missing reference to: -

Microsoft Office 14.0 Access database engine Object Library

When I delete that reference the Date() function is recognised.

That's a significant missing reference. If it's an MDB, that's the wrong reference (it should be DAO 3.6). But if it's an ACCDB and it's A2007, then you need to re-register that DLL. One way to do this is to manually search for it with the browse button found in the references dialog and to choose it.

 

[SeanB]

It's Access 2003. I'll look for it and see if I can do that.

 

[SeanB]

I just noticed that in DB1 John Big Booty gave me that if you change the ages so they are all different i.e. not having 3 at the same age like 32 then it will only count those the same age or if they are all different only count 1 of them.
I have managed to recreate that db and it acts just like it. Unfortunately I need it to group all the ages together and cant have it excluding any.

 

[John Big Booty]

I've made a small change to Query2. I've changed it to a Totals query. It now Groups By the age groups, and Sums the count of ID. I think it should now produce the result you are after.

 

[SeanB]

Yep thats it.
I am impresssed at how you simply change it when you need to.
I've decided to go to the local adult college to learn how to use access properly. Unfortunately that wont start til Sept so in the meantime I'll have to figure this out myself as they need the db asap.

 

[SeanB]

SUCCESS!!!
I did it. Managed to put it into my db.
I started it on office 2010 beta then switched to Access 2003 as that is what the computers running this db have installed on it.
I believe this lead to a missing reference in Microsoft Office 14.0 Access database engine Object Library as the .dll it needs is newer than what is installed.
I swithced it to DAO 3.6.
Also I believe I wasn't using enough ")" at the end of the EXPR2 command.

All I need to do now is figure out how to open a sub from with the results displayed.
As it is I am very chuffed :-D

 

[dfenton]

It's Access 2003. I'll look for it and see if I can do that.

The correct DAO reference for A2003 is DAO 3.6. Looks like you may have run multiple versions of Access, which changes the registration, and can cause A2003 to hook up with the wrong Access components.

 

[SeanB]

I'll be sure not to do that again.

 

[Vassago]

Glad you got it sorted out!

 

[John Big Booty]

...

I've decided to go to the local adult college to learn how to use access properly.

.......

Good work, you should enjoy it

 

[SeanB]

I am struggling to get it to display the collected results. I can't get it to see the gathered results. If I could I'm confident I can make the subform to display them.
Trying to follow the sql code in db1 with forms 1&2 and form 1 has code for command 4 and 5 yet I cannot see where the command buttons are?