Hi Wayne,
Sorry its taken me so long to get back to you. I havent been well the past couple of weeks and just couldnt get on the pc. Today having felt better than I have for a while, was itching to just get back on and give this another go with your reply below giving such helpful detail, IT WORKS!!!!!!
Thank you so much for all your help.
I really appreciate it.
regards
Julie
Ok, you're using the third example, which is a Sub not a Function. It relies on the values being passed "by reference" (which is the default for VBA). The sub doesn't return a value. What it will do is modify the variables that you've passed to it. It wouldn't be good as a control source item, but does calculate the values you want, so let's do some modifying. Also, don't change the names of the variables in the Sub/Function. If they line up, left to right, with what you pass, that's all you need. The names are used internally in the sub/function and if you change them in the title line, you'll have to change them all the way through the sub/function.
For the modification, I will change the sub to a function and have it return a text string showing years and months.
'--- CODE START ---
'Public Sub CalcAge(vDate1 As Date, vdate2 As Date, ByRef vYears As Integer,ByRef vMonths As Integer, ByRef vDays As Integer)
Public Function CalcAge2(vDate1 As Date, Optional vDate2 As Variant) As String
' Comments : calculates the age in Years, Months and Days
' Parameters:
' vDate1 - D.O.B.
' vDate2 - Date to calculate age based on
' vYears - will hold the Years difference
' vMonths - will hold the Months difference
' vDays - will hold the Days difference
Dim vYears As Integer, vMonths As Integer, vDays As Integer
If IsMissing(vDate2) Then vDate2 = Date
vMonths = DateDiff("m", vDate1, vDate2)
vDays = DateDiff("d", DateAdd("m", vMonths, vDate1), vDate2)
If vDays < 0 Then
' wierd way that DateDiff works, fix it here
vMonths = vMonths - 1
vDays = DateDiff("d", DateAdd("m", vMonths, vDate1), vDate2)
End If
vYears = vMonths \ 12 ' integer division
vMonths = vMonths Mod 12 ' only want leftover less than one year
CalcAge2 = vYears & " Years, " & vMonths & " Months"
End Function
'--- CODE END ---
By making the 2nd date Optional, you can skip the second date and it will assume the current date. If you are doing this for a show on a future date, you could pass the date of the show here and the result would be for that date.
To call this function, use this control source:
=CalcAge2([DOB], [ShowDate])
or to use the current date automatically:
=CalcAge2([DOB])
--
Wayne Morgan
MS Access MVP
Julie Nicole said:
You know Ive been up all night on this and it would only take you a minute!
Really appreciate your help, thanks for the encouragement aswell.
Ok, pasting the code into the module is easy
BUT, in the code it uses vdate1as D.O.B and vdate2 as other date which is
the show date. (what we need is the actual age of the cat by year and month
to make sure it enters the right ring on that show date)
So, because Ive used DOB and SDate. I changed the vdate1 to DOB and vdate2
to SDate to they refer to my fields, is this correct? Or is that totally
wrong ?)
Placing the control source is easy
LOL
But it comes up blank and when I put it in again, close and reopen, its gone
again!
Was that an example or the actual control source?
There is no Age in the list under where the DateDiff thing is when you look
up functions.
Also, does the "control"
have to have a format of either date or number,
and a name?
Im nearly there...I think!
thanks
Julie
Wayne Morgan said:
Yes, you're on the right track. First, a field is an item in table, a
control is an item on a form, such as a textbox. It frequently displays the
value of a field from a table.
The code would be placed in a module as a Public Function. Go to the modules
tab in the database window and create a new module. Place the Age code there
and save the module. Don't give the module the name of anything else,
especially not the name of any of the procedures in the module. The default
of Module1 will work, but I like to give things descriptive names when
possible.
Next, on your form, create an unbound textbox. For its control source, you
would insert and equation that calls the Age function and passes the value
of the DOB.
Example Control Source:
=Age([DOB])
A Public function is called and works just as the built in VBA functions do
(such as Left("text", 2), Date(), Round(number), etc).
--
Wayne Morgan
MS Access MVP
Julie Nicole said:
Thanks but how do I use the code. I am only new to this and having a code
is one thing but where to put it is another!
I have a field with the DOB and a field with the show date both set to type
date. Do I need another field on the form that the code puts its answer in?
and if so what do I call this field so the code knows it?
