UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> Calculate The Years To Today's Date, Access 2007    
 
   
frm
post Sep 30 2019, 08:54 AM
Post#21



Posts: 126
Joined: 20-March 18



Hi isladogs

I used the example already made and inside I inserted a new frmAge1 form and a new Modulo2 Module where I put your functions.

I need to enter the patient's age in the Table1 table because every year I have to do a patient statistic by age.
Go to the top of the page
 
isladogs
post Sep 30 2019, 09:29 AM
Post#22


UtterAccess VIP
Posts: 1,808
Joined: 4-June 18
From: Somerset, UK


No you don't need to save the age for your statistic and should not do so..
Just run a query with AgeYears([DOB]) as a query field.

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
arnelgp
post Sep 30 2019, 09:58 AM
Post#23



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


you can run it on a query or use the form's current event and the dob's textbox beforeupdate event.
Attached File(s)
Attached File  sampleAge_V1.zip ( 29.42K )Number of downloads: 7
 

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
frm
post Sep 30 2019, 10:39 AM
Post#24



Posts: 126
Joined: 20-March 18



Hi isladogs

how do I statistically detect how many patients have for example 55 years, 68 years, ... ..?

^^^^^^^^^^^^

Hi arnelqp,

OK the new example.

I changed the clock to the computer by putting the date 30 September 2020 and the patient years remain the ones that were calculated in 2019.

How do I update the current year?
Go to the top of the page
 
arnelgp
post Sep 30 2019, 10:57 AM
Post#25



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


on the form, you need to go to that record.
but as isladog said, you do not need to save
the age to the table. you can create a query
and calculate it on the fly:

select patientid, [first name], [last name], [dob], Age([dob]) As age from yourTable;



on your new question you can get the summary of ages (note the Age() is the function we made earlier).

select Age([DOB]) As Age, Count(Age([DOB])) As [Count] from yourTable Group By Age([DOB]);
This post has been edited by arnelgp: Sep 30 2019, 10:58 AM

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
frm
post Sep 30 2019, 11:29 AM
Post#26



Posts: 126
Joined: 20-March 18



OK

Now, how can I solve this other problem?

I changed the clock to the computer by putting the date 30 September 2020 and the patient years remain the ones that were calculated in 2019.

How do I update the current year?
Go to the top of the page
 
arnelgp
post Sep 30 2019, 11:49 AM
Post#27



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


many times did we tell you, you do not need to save the Age of the person.
you do it in a query.

if still, you want to do it, add code to the Form's Open event:
CODE
private sub form_open(cancel as integer)

currentdb.execute "update yourTable set years=fnAge([dob]);"

end sub

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
frm
post Sep 30 2019, 01:15 PM
Post#28



Posts: 126
Joined: 20-March 18



OK

Can you give me any suggestions on how to solve what I asked for in this

Post#26

Thanks
Go to the top of the page
 
isladogs
post Sep 30 2019, 01:31 PM
Post#29


UtterAccess VIP
Posts: 1,808
Joined: 4-June 18
From: Somerset, UK


If as advised you are calculating the age in a query, pressing F5 to refresh the query should work after changing the system date.
If its already saved in a table then you should now understand why we've been saying not to do that.
If it is a table field, delete it and do it in the query.

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
frm
post Sep 30 2019, 01:32 PM
Post#30



Posts: 126
Joined: 20-March 18



Sorry,

I entered your code on Form's Open event, but this line

currentdb.execute "update yourTable set years = fnAge ([dob]);"

it turns yellow.
Go to the top of the page
 
frm
post Sep 30 2019, 01:36 PM
Post#31



Posts: 126
Joined: 20-March 18



Excuse me,

but at this point I don't understand how to go on anymore.

I said I have little knowledge of vba.
Go to the top of the page
 
isladogs
post Sep 30 2019, 01:40 PM
Post#32


UtterAccess VIP
Posts: 1,808
Joined: 4-June 18
From: Somerset, UK


My last attempt.
Arnel only mentioned that code if you are determined to save the age in a table.
Doing so is a mistake as we keep telling you.
If you must use the update query, replace fnAge([DOB]) with whatever function name you are using e.g AgeYears.
But that update query will run each time you open the form which is a waste of time and resources.

I'll drop out of this thread now. Good luck

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
frm
post Sep 30 2019, 02:31 PM
Post#33



Posts: 126
Joined: 20-March 18



Once again I explained myself badly.

I am trying to follow your precious advice but I have not yet figured out how to put it into practice.

I don't want to take away precious time, but in all honesty I don't know how to do it.

Switching from theory to practice may seem easy, but for me it is not.

That's all.

Anyway, thanks to all of you for your time.
Go to the top of the page
 
missinglinq
post Oct 9 2019, 07:00 AM
Post#34



Posts: 4,640
Joined: 11-November 02



Frequently, when a Function pops an error, the Module it resides in has been given the same name as the the Function itself...is that the case here? If so...name the module something else...such as modAge.

Linq ;0)>

--------------------
Hope this helps!

The problem with making anything foolproof...is that fools are so darn ingenious!

All posts/responses based on Access 2003/2007
Go to the top of the page
 
frm
post Oct 11 2019, 06:52 AM
Post#35



Posts: 126
Joined: 20-March 18



Hi,

I followed your suggestions and created the Query2 and FormAge2.

The Query2 in SQL view has this statement:

SELECT Table1.ID, Table1.FullName, Table1.DOB, DateDiff("yyyy",[DOB],Date())-IIf(Format([DOB],"mmdd")>Format(Date(),"mmdd"),1,0) AS Years
FROM Table1;

Calculate age in the Age text box on the FormAge2.

I have also tried to change the PC clock to see if the years change with the change of the year.

It works.

A question: in this way the years can be inserted in Table1 one or not?

Attached File  sampleAge_V2.zip ( 31.47K )Number of downloads: 1


Thanks


Go to the top of the page
 
RJD
post Oct 11 2019, 07:21 AM
Post#36


UtterAccess VIP
Posts: 10,132
Joined: 25-October 10
From: Gulf South USA


Hi frm: PMFJI, but I recommend that you DO NOT update the table with Age. This value should be calculated each time you need it, and should, for convenience, use a Function to get it. If you store a field (e.g. Age) that has to be constantly checked and updated, the chance of error (failing to update in a timely manner) is high.

Age: GetAgeYears([DOB])

Please note that I changed the GetAgeYears function to return a numeric value rather than text, and changed Query2 to use that function. Using a numeric value insures that you can easily calculate such things as Age Groups, Persons Older Than X, etc. I use this approach frequently in several client databases. You can easily add words (e.g. =Age & " Years") for display in forms or reports if that is necessary.

Using this approach will assure the correct age at all times.

If there is ever a need to "freeze" the age (as when a person is deceased), that can easily be done with a field in the table to indicate that status and the calculation logic can be written to accommodate that, without ever storing the age value.

Just some thoughts and suggestions ...

HTH
Joe
Attached File(s)
Attached File  sampleAge_V2_Rev1.zip ( 27.58K )Number of downloads: 6
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
frm
post Oct 11 2019, 08:46 AM
Post#37



Posts: 126
Joined: 20-March 18




Thanks to all the experts for the valuable suggestions they gave me.

I have eliminated from the Table1 the Age field and the age calculation I do it by query as you have recommended me all.

If one turns to the forum, like myself, he must follow the experts' suggestion otherwise it makes no sense

and it is not even correct to take away valuable time from other people.

Thanks
Go to the top of the page
 
RJD
post Oct 11 2019, 01:22 PM
Post#38


UtterAccess VIP
Posts: 10,132
Joined: 25-October 10
From: Gulf South USA


You are very welcome, from all of us. I think you have made the correct decision to calculate the age, using a function, whenever you need it.

We wish you continued success with your project. Let us know if we can be of further assistance...

Regards from us all,

Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
frm
post Oct 11 2019, 01:32 PM
Post#39



Posts: 126
Joined: 20-March 18



Ok
fundrink.gif
Go to the top of the page
 
2 Pages V < 1 2


Custom Search


RSSSearch   Top   Lo-Fi    18th November 2019 - 04:34 AM