help with my function

[pb21]

I ahve a function that prepares a date for a get age function but mine is complaining :
compile error byref argument type mismatch.

I can see why I have the error but I am not sure how to correct it.

regards
Peter

Public Function getLearndirectAge(enrolmentdate As String, Dob As String) As Integer

Dim enrolmentDateIn, dobin As Date
Dim learndirectAge As Integer
Dim StrYear, StrPreviousYear, StrAugust As String


StrPreviousYear = Format(Now(), "yyyy") - 1 & "#"
StrAugust = "#31/8/" & StrPreviousYear
dobin = "#" & dob & "#"

' i think the problem his here with the format of my dates
getLearndirectAge = dhAge(dobin, StrAugust)


End Function

Public Function dhAge(dtmBD As Date, Optional dtmDate As Date = 0) _
As Integer

' From "Visual Basic Language Developer's Handbook"
' by Ken Getz and Mike Gilbert
' Copyright 2000; Sybex, Inc. All rights reserved.

' Thanks to Peter Mundy, on comp.databases.ms-access
' for fixing up a flaw in the original logic of this
' procedure.

Dim intAge As Integer

If dtmDate = 0 Then
' Did the caller pass in a date? If not, use
' the current date.
dtmDate = Date
End If
intAge = DateDiff("yyyy", dtmBD, dtmDate)
If dtmDate < DateAdd("yyyy", intAge, dtmBD) Then
intAge = intAge - 1
End If
dhAge = intAge
End Function

 

[Brianwarnock]

You have defined Dobin as a date and equate it to a string, could you not have coded Dobin = Datevalue([Dob]), and wouldn't it have been better to use Date() rather than Now().

Brian

 

[ColinEssex]

also I can't see where EnrolmentDate is, yet its declared at the top as a string

Col

 

[Brianwarnock]

I have assumed that enrolementdate and dob are passed as strings to the function

Brian

 

[pb21]

function problem

Hi Brian thanks

I took your advice and made the changes but its still returning the same error.

Public Function getLearndirectAge(Dob As String) As Integer
Dim dobIn As String
Dim dobOut
Dim StrPreviousYear, StrAugust As String

'get the date of birth and pass to variable
dobIn = Dob

'get the current year less one
StrPreviousYear = Format(Date, "yyyy") - 1 & "#"
'create the date string as 31/8/previous year and convert to date format
StrAugust = DateValue("#31/8/" & StrPreviousYear)
'create the date of birth string and convert to date format
dobOut = DateValue("#" & dobIn & "#")

'now pass to function get age which will return the learners age on 31/8 or the previous year
getLearndirectAge = dhAge(dobOut, StrAugust)

'************** failing on dobOut on the line above '*****************
End Function

 

[modest]

Dim enrolmentDateIn, dobin As Date
Dim learndirectAge As Integer
Dim StrYear, StrPreviousYear, StrAugust As String

The bold variables are declared as type Variant because you haven't associated them with a variable type (they don't declare the same as in C).

To get what you probably want it would be like:

Dim enrolmentDateIn [B]As Date[/B], dobin As Date
Dim learndirectAge As Integer
Dim StrYear [B]As String[/B], StrPreviousYear [B]As String[/B], StrAugust As String

OR----->

Dim enrolmentDateIn As Date
Dim dobin           As Date
Dim learndirectAge  As Integer
Dim StrYear         As String
Dim StrPreviousYear As String
Dim StrAugust       As String

 

[pb21]

function problem

In answer to the question I have altered it as I only need to pass in the date of birth, its the second function that takes date of birth and a secondary date to give me the persons date of birth on 31st of august in the preceeding year to the current year.

I am calling the function from the access query grid in form of
age on 31st aug: getlearndirectage([dateofbirth])

regards

 

[Brianwarnock]

Remove the # dob is presumably a string else no work required.

Dobout=datevalue(dob) and you will not need # on other string for Datevalue to convert the string to a date .

Brian

 

[pb21]

function problem

Thanks to everyone. It now functions correctly.

Many regards

Peter

 

[Mile-O]

Dim enrolmentDateIn, dobin As Date
Dim learndirectAge As Integer
Dim StrYear, StrPreviousYear, StrAugust As String

The bold variables are declared as type Variant because you haven't associated them with a variable type (they don't declare the same as in C).

To get what you probably want it would be like:

Dim enrolmentDateIn [B]As Date[/B], dobin As Date
Dim learndirectAge As Integer
Dim StrYear [B]As String[/B], StrPreviousYear [B]As String[/B], StrAugust As String

OR----->

Dim enrolmentDateIn As Date
Dim dobin           As Date
Dim learndirectAge  As Integer
Dim StrYear         As String
Dim StrPreviousYear As String
Dim StrAugust       As String

You never know, he may have the following atop his module.

DefDate D-E
DefStr S

 

[modest]

You never know, he may have the following atop his module.

DefDate D-E
DefStr S

If this is the case then take away the "As Date" behind dobin and take away the "As String" behind StrAugust