Here is what Age is (you were right it was there)
' FUNCTION NAME: Age()
'
' PURPOSE:
' Calculates age in years from a specified date to today's date.
'
' INPUT PARAMETERS:
' StartDate: The beginning date (for example, a birth date).
'
' RETURN
' Age in years.
'
'*************************************************************
Function Age(varBirthDate As Variant) As Integer
Dim varAge As Variant
If IsNull(varBirthDate) Then Age = 0: Exit Function
varAge = DateDiff("yyyy", varBirthDate, Now)
If Date < DateSerial(Year(Now), Month(varBirthDate), _
Day(varBirthDate)) Then
varAge = varAge - 1
End If
Age = CInt(varAge)
End Function
'*************************************************************
' FUNCTION NAME: AgeMonths()
'
' PURPOSE:
' Compliments the Age() function by calculating the number of months
' that have expired since the last month supplied by the specified date.
' If the specified date is a birthday, the function returns the number
of
' months since the last birthday.
'
' INPUT PARAMETERS:
' StartDate: The beginning date (for example, a birthday).
'
' RETURN
' Months since the last birthday.
'*************************************************************
Function AgeMonths(ByVal StartDate As String) As Integer
Dim tAge As Double
tAge = (DateDiff("m", StartDate, Now))
If (DatePart("d", StartDate) > DatePart("d", Now)) Then
tAge = tAge - 1
End If
If tAge < 0 Then
tAge = tAge + 1
End If
AgeMonths = CInt(tAge Mod 12)
End Function
Klatuu said:
Okay, thanks.
So, if LicHeldDate = Null Then the driving test was not passed. If
that
is
correct and you were using this directly instead of the Text503, you
might
get the right result. It appears the Age() function is returning a 0
to
Text503, which makes it impossible to determine whether the driver has
not
passed the test or has been driving less than a year. If you have
visibility
to LicHeldDate, you might try using that instead of Text503.
Have you gone into your VB editor and done a search for Age to
determine
what it is doing?
:
This code gives the full set of rules
The LicHeldDate field is the date the driving test was passed.
Then there is a field DateOfAccident
The licence held peiod is the time difference between the two dates.
Dim crit As String
Dim ed As Currency
Dim strLookup As String
crit = "[CustomerID]=" & Me.CustomerID
Select Case Me.ExAge
Case Is < 21
strLookup = IIf(Me.CarCommercial = "Private car", _
"[EMExcess]+[Under21]",
"[EMCommercialExcess]+[Under21]")
Case 21 To 24
strLookup = IIf(Me.CarCommercial = "Private Car", _
"[EMExcess]+[Under25]",
"[EMCommercialExcess]+[Under25]")
Case Else
strLookup = IIf(Me.CarCommercial = "Private Car", _
"[EMExcess]", "[EMCommercialExcess]")
End Select
ed = DLookup(strLookup, "qryCheckRecSent", crit)
If Nz(Me.Text503, 99) < 1 And Me.ExAge >= 25 Then
ed = ed + DLookup("[Novice25Plus]", "qryCheckRecSent", crit)
End If
Me.ExcessDue = ed
Hope this makes it a bit clearer
Bob
That would be hard without it in front of me and because I don't
know
all
the
business rules.
So this is an insurance claim system? Are you interested in
comparing
the
date of an accident or claim, or how long the driver has had a
license?
As I recall, the drive may not have passed the test (don't know what
test
we
are talking about) or may not be associated as a driver for the
vehicle.
Is
this correct? If so, what do you do in that case, or is this what
we
are
trying to determine?
:
That's is how it works though I am sure it checks with the date the
claim
was made for period the licence was held. Have you any ideas on how
I
should
go about setting it up because I must admit I am stuck.
Bob
I'll bet if you look for Age it is a function that uses the date
funtion
to
look at the date a license was issued and compares it to the
current
date
and
returns a value. The problem you have here is that if the driver
is
25
or
older and has had his license for less than a year, the value
will
be
0.
It
appears it will also be 0 if the driver has not passed the test
or
no
driver
is associated with the vehicle. You will have to have a way to
tell
the
difference.
:
Age is the time a driver has held a licence, sorry if thats not
the
answer
you were looking for then I am unsure where age comes from there
are
no
Age
fields.
Bob
You said Text503 is the control with the control source
=Age([LicHeldDate])
So, what is Age?
:
Klatuu its a text field with the following as the control
source
=Age([LicHeldDate])
LicHeldDate is a Date/Time field
Does that help?
Bob
I need to know what age() is. Is it a user defined
function?
can
you
post
the code for it?
:
Klatuu sorry if there is no data in field LicHeldDate
(because
we
do
not
know when the driver passed the test or there is not a
driver
allocated
to a
vehicle).
Text503 returns 0 (I though it was picking up a null blank
cell
but
its
not)
Text503 has the following control source
=Age([LicHeldDate])
So the code picks up the <1 part of the Excess
"Novice25Plus"
(Which
is
wrong if there is no driver allocated to a vehicle or no
date
in
LicHeldFor)
But if the driver has held a licence for less than a full
year
Text503
returns 0 so how do I get Text503 to be null if there is
no
data
in
Field
LicHeldDate? Because this is the only way I can see that
it
will
work
correctly with the amended code you have just posted. Or
is
there
a
better
way round the problem.
Thanks for your help
Bob
message
I have not tested this, but I think it will do the trick.
I
return
99
in
the
Nz() function so that a Null value will translate to a
value
larger
than
you
are checking for.
ed = DLookup(strLookup, "qryCheckRecSent", crit)
If Nz(Me.Text503,99) < 1 And Me.ExAge >= 25 Then
ed = ed + DLookup("[Novice25Plus]",
"qryCheckRecSent",
crit)
End If
:
Hi I have the following code which I need to work out
how
to
tell
it
if
Text503 is null then to ignore this part of the code
If Me.ExAge >= 25 And Me.Text503 <1 Then
ed = ed + DLookup("[Novice25Plus]",
"qryCheckRecSent",
crit)
End If
I have tried different ways but all produce the
following
error
Compile
Error Expected Expression
this is what I have done, how do I put it right?
ed = DLookup(strLookup, "qryCheckRecSent", crit)
If Me.ExAge >= 25 And Me.Text503 Is Not Null
And
<1
Then
ed = ed + DLookup("[Novice25Plus]",
"qryCheckRecSent",
crit)
End If
Any help would be very much appreciated.
Bob
