VBA code between an Access table and Excel Button

[J355Y1]

Hi,
Im not sure if this is the right place to post this but i dont know what else to do as i am a very newbie to Access so ill just ask anyways,

I have a button in Excel that needs to use data from an Access table and then use that data to find information to then update that same Access table.

Can someone please help me with all the code required for this button.

1. What exact code would i need to get the Current date?

2. What code would i need to then use that date to calculate the difference from that date and the DOB field in my Access table?

3. How would i round the difference to the closest difference in years?

4. What code would i need to then use the DOB in years to update the age group field in the Access table?

Thanks very much for your help, this is way over my head!!

 

[DCrake]

If you hold a persons DOB in a database then you can calculate an age group on the fly. There is no need to store this in a table as the age group can change from day to day for different people.

First you would have a function that calulates their age then a second function that places that person in a specific age group. The age groups should be held in a table also so that is can be manipulated at a later date if needed. Future Proofing.

 

[JANR]

1. To get current date use the Date() function ex: somevariable = Date
2. To calculate diffence in dates use the DateDiff function. ex. somevariable = datediff("d", [DOB], Date) gives you
number of days since DOB for more accurate calculations search this site.
3. somevariable = Round(datediff("d",[DOB], date)/365.25)
4. You don't STORE a calculated value in an Access table, the agegroup field is derived front your DOB field and you calculate that on the fly when you need it.

JR

 

[J355Y1]

Thanks JANR but could you please break it down for me a little more..

1. To get current date use the Date() function ex: somevariable = Date
2. To calculate diffence in dates use the DateDiff function. ex. somevariable = datediff("d", [DOB], Date) gives you
number of days since DOB for more accurate calculations search this site.
3. somevariable = Round(datediff("d",[DOB], date)/365.25)
4. You don't STORE a calculated value in an Access table, the agegroup field is derived front your DOB field and you calculate that on the fly when you need it.

What do you mean by "d"? Is it suppose to be written like that or am i suppose to replace that with something? Also there are no calculated fields in my database only dates, so im still stuck on my question 4 of how i would update my AgeGroup field after completing all the other steps.

Im really new to this so i need alot more explaining and tutoring to understand.
Thanks heaps.

 

[JANR]

the "d" stands for days, so no changes to the expression.

see Access help for other parameters you can use with datediff.

Also there are no calculated fields in my database only dates, so im still stuck on my question 4 of how i would update my AgeGroup field after completing all the other steps.

Yes there will be if you insist on updating you AgeGroup field, that will be a calculated field in your table. Consider this:

You will have to update this field EVERY day because tomorrow the persons in your database will be 1 day older and some of them might get into another AgeGroup. In short you can't trust your data if you query your data.

The calculated AgeGroup field only belongs as a calulated field in a Query or as an unbound control on a form or a report. As a bonus modern computers will calculate the AgeGroup faster in memory than it will use on a diskfetch (in most cases).

To calculate your AgeGroup use a function in a Standard Module so it can be always accessed.

Function AgeGroup(DOB As Date) As Integer
Select Case Round(DateDiff("d", DOB, Date) / 365.25)
    Case 0 To 20
        AgeGroup = 1
    Case 21 To 40
        AgeGroup = 2
    Case 41 To 60
        AgeGroup = 3
    Case Else
        AgeGroup = 4
End Select
End Function

You can call this function form a query or a unbound control in a form or report.

in a query:

Age Group: AgeGroup([DOB])

in a textbox on form/report, put this in the ControlSource:

=AgeGroup([DOB])

JR