I'm using Access 2007 (.accdb) to create a front-end to my SQL Server 2008 database. I've got a vba module in a form that needs to calculate a person's age based on the data entered on the form. I have what I think is a global function for calculating the age - it lives under the modules section under that database, and it's declared as a Public Function. But when the function gets called from the form's module, it gives me this error:
"Expected variable or procedure, not module"
If I put the function in the form's code - still as a separate function from the sub, rather than just the code in the sub, if you know what I mean - it works just fine. But it's such a basic function that I will probably need to call from several different places, I really would like to getting it working as a global function.
Here's the function:
Public Function AgeInYears(startDate As Date, endDate As Date) As Integer
AgeInYears = DateDiff("yyyy", startDate, endDate)
If DatePart("y", startDate) >= DatePart("y", endDate) Then
AgeInYears = AgeInYears - 1
And then the sub attached to the form:
Private Sub CollectionDate_AfterUpdate()
Dim calculatedAge As Integer
If Not IsNull(Me.CollectionDate) And Not IsNull(Me.DOB) And Not IsNull(Me.CollectionAge) Then
calculatedAge = AgeInYears(Me.DOB, Me.CollectionDate)
If calculatedAge <> Me.CollectionAge Then
MsgBox ("Collection Age is " & Me.CollectionAge & "," & vbCrLf & "But calculatedAge is " & calculatedAge & "!")
Thanks in advance,