VBA Function Referencing a Form (2000/03)

Topic

New Reply

(Edited by HansV to make URL clickable – see Help 19)

Hello – I wish to use the MS Age Function in a slightly different way to that which MS intended – (Refer http://support.microsoft.com/kb/290190%5B/url%5D)

Specifically I do not wish to calculate the age from date of birth to now but wish to use the function to use a value stored on an input form (Me.txtTestDate) instead of Now

Existing function in part:

varAge = DateDiff("yyyy", varBirthDate,  Now )

The blue value is what I want to be taken from the input form. I have simply tried replacing Now with Me.txtTestDate but it doesn’t know about the form. I have had very little experience with functions.

Can this be done please?

Leigh

Reply | Quote

Replies

You can use Me only in the module behind the form itself. Assuming that your function is in a standard module, you’d use

Forms!NameOfForm!txtTestDate

where NameOfForm is the name of the form. If this name contains spaces or punctuation, enclose it in square brackets: [Name Of Form].

Reply | Quote

Thanks for your quick reply, Hans. What do you mean by a standard module?

I tried changing the reference as suggested but it runs to that line and then states that “..can’t find the field txtTestDate”

This is the reference I used ‘varTestAge = DateDiff(“yyyy”, varTestDate, Forms!Students!txtTestAge)’ when it came up with the message.

The form name is ‘Students’ the field name is ‘txtTestDate’. Have I got the reference correct?

Reply | Quote

A standard module is the kind of module you create by clicking the New button in the Modules section of the Database window, or by selecting Insert | Module in the Visual Basic Editor.

Referring to Forms!Students!txtTestDate requires that

a) the form named Students is open.
the form contains a control (text box) whose name is txtTestDate.

Reply | Quote

Well it is not a Class module so I assume that it is a standard one.

Function TestAge(varTestDate As Variant) As Integer

    Dim varTestAge As Variant

    If IsNull(varTestDate) Then TestAge = 0: Exit Function

'    varTestAge = DateDiff("yyyy", varTestDate, Now)
    
    varTestAge = DateDiff("yyyy", varTestDate, Forms!Students!txtTestAge)
    If Date < DateSerial(Year(Forms!Students!txtTestAge), Month(varTestDate), _
        Day(varTestDate)) Then
        varTestAge = varTestAge - 1
    End If
    
    TestAge = CInt(varTestAge)

End Function

And the form is open and the field is named txtTestDate

Sould I post the db?

Reply | Quote

Oops – if the text box is named txtTestDate you should use Forms!Students!txtTestDate instead of Forms!Students!txtTestAge

Reply | Quote

I might just take more notice of the written word…

Thanks Hans, we are getting closer but now the age difference which is what I needed for calcs is way out. I am getting a difference of the order of 90+ years.

I thought it might be as simple as taking a bigger value from a small value but it wasn’t. The code that calls the TestAge/TestAgeMonths functions is shown below.

=IIf(IsNull([txtDOB])," ",TestAge(([txtTestDate]-[txtDOB])) & "." & TestAgeMonths(([txtTestDate]-[txtDOB])))

In effect I am trying to get the age at which the testing was done (previously I used system date) but the code is not working correctly.

Any suggestions?

Reply | Quote

You should feed a date to TestAge (and presumably to TestAgeMonths), not the difference of two dates.

Reply | Quote

Try this version of the function. It takes two arguments instead of one: the date of birth and the test date:

Function TestAge(varDOB As Variant, varTestDate As Variant) As Integer
Dim intTestAge As Integer

If IsNull(varDOB) Or IsNull(varTestDate) Then
Exit Function
End If

intTestAge = DateDiff("yyyy", varDOB, varTestDate)
If varTestDate < DateSerial(Year(varTestDate), Month(varDOB), Day(varDOB)) Then
intTestAge = intTestAge - 1
End If
TestAge = intTestAge
End Function

You’d use it like this:

=TestAge([txtDOB],[txtTestDate])

Reply | Quote

Here is the corresponding function for months:

Function TestAgeMonths(varDOB As Variant, varTestDate As Variant) As Integer
Dim intTestAge As Integer

If IsNull(varDOB) Or IsNull(varTestDate) Then
Exit Function
End If

intTestAge = DateDiff("m", varDOB, varTestDate)
If Day(varTestDate) < Day(varDOB) Then
intTestAge = intTestAge - 1
End If
TestAgeMonths = intTestAge Mod 12
End Function

Use like this:

=TestAgeMonths([txtDOB],[txtTestDate])

Reply | Quote

Thanks Hans, again your guidance and code has worked a treat. I do appreciate your assistance.

Have a Heineken on me (PayPal OK?)

Reply | Quote