Slight Script change!

[Bob]

This script calculates the horses age and adds another year on every Aug
1st, I want it to do this till the horse is 10 then anything after will just
be an (X) ie: 11 will be replaced with X.............Thanks Bob

=IIf([cbDateOfBirth]="" Or
IsNull([cbDateOfBirth]),"",funCalcAge(Format("01-Aug-" &
[cbDateOfBirth],"dd/mm/yyyy"),Format(Now(),"dd/mm/yyyy"),1))

Thanks in advance.........Bob Vance

 

[Steve Schapel]

Bob,

Does that really work? It is difficult to see what it is all about, to
be honest. Am I right in assuming that the entry in cbDateOfBirth is a
actually a year of birth? And it's a Text field, not a number? Anyway,
whatever the case, it would seem to me that the modification you require
would be a modification to the funCalcAge function, which is apparently
a user-defined function in your database. If you could post back with
the funCalcAge code, someone may be able to advise.

 

[Bob]

Hope this helps Steve,
Thanks
Else
.Fields("DateOfBirth") = Format(CDate(tbDateOfBirth.value),
"mm/dd/yyyy")
.Fields("HorseDetailInfo") = tbFatherName.value & "--" &
tbMotherName.value & "--" & funCalcAge(Format(tbDateOfBirth.value,
"dd-mmm-yyyy"), Format("01-Aug-" & Year(Now()), "dd-mmm-yyyy"), 1) & " -- "
& tbSex.value
End If

Bob,

Does that really work? It is difficult to see what it is all about, to be
honest. Am I right in assuming that the entry in cbDateOfBirth is a
actually a year of birth? And it's a Text field, not a number? Anyway,
whatever the case, it would seem to me that the modification you require
would be a modification to the funCalcAge function, which is apparently a
user-defined function in your database. If you could post back with the
funCalcAge code, someone may be able to advise.

--
Steve Schapel, Microsoft Access MVP

This script calculates the horses age and adds another year on every Aug
1st, I want it to do this till the horse is 10 then anything after will
just be an (X) ie: 11 will be replaced with X.............Thanks Bob

=IIf([cbDateOfBirth]="" Or
IsNull([cbDateOfBirth]),"",funCalcAge(Format("01-Aug-" &
[cbDateOfBirth],"dd/mm/yyyy"),Format(Now(),"dd/mm/yyyy"),1))

Thanks in advance.........Bob Vance

 

[Bob]

Oops this must be it:
Function funCalcAge(dtDOB As Date, dtNow As Date, Optional nFormat As
Integer = 3) As String
Dim nYears As Integer, nMonths As Integer, nDays As Integer
dtDOB = Format(dtDOB, "dd/mm/yyyy")
dtNow = Format(dtNow, "dd/mm/yyyy")
If Day(dtDOB) > Day(dtNow) Then
nDays = DateDiff("y", dtDOB, dtNow) + DateDiff("y", DateAdd("m",
DateDiff("m", dtDOB, dtNow) - 1, dtDOB), dtDOB)
If Month(dtDOB) > Month(dtNow) - 1 Then
nYears = DateDiff("yyyy", dtDOB, dtNow) - 1
nMonths = DateDiff("m", dtDOB, dtNow) + DateDiff("m",
DateAdd("yyyy", nYears, dtDOB) - 1, dtDOB) - 1
Else
nYears = DateDiff("yyyy", dtDOB, dtNow)
nMonths = DateDiff("m", dtDOB, dtNow) + DateDiff("m",
DateAdd("yyyy", nYears, dtDOB), dtDOB) - 1
End If
Else
nDays = DateDiff("y", dtDOB, dtNow) + DateDiff("y", DateAdd("m",
DateDiff("m", dtDOB, dtNow), dtDOB), dtDOB)
If Month(dtDOB) > Month(dtNow) Then
nYears = DateDiff("yyyy", dtDOB, dtNow) - 1
nMonths = DateDiff("m", dtDOB, dtNow) + DateDiff("m",
DateAdd("yyyy", DateDiff("yyyy", dtDOB, dtNow) - 1, dtDOB), dtDOB)
Else
nYears = DateDiff("yyyy", dtDOB, dtNow)
nMonths = DateDiff("m", dtDOB, dtNow) + DateDiff("m",
DateAdd("yyyy", DateDiff("yyyy", dtDOB, dtNow), dtDOB), dtDOB)
End If
End If
Select Case nFormat
Case 1:
If nYears <= 0 Then
'funCalcAge = "Less Than Year"
funCalcAge = " 0 yo"
Else
funCalcAge = " " & nYears & " yo"
End If
Case 2: funCalcAge = IIf(nYears > 0, " " & nYears & " yrs, ", "") &
IIf(nMonths > 0, nMonths & " M", "")
Case 3: funCalcAge = IIf(nYears > 0, " " & nYears & " yrs, ", "") &
IIf(nMonths > 0, nMonths & " M,", "") & IIf(nDays > 0, nDays & " D", "")
End Select
End Function

 

[Steve Schapel]

Bob,

Assuming everything else is working to your satisfaction, I think this
will do it...

Instead of this:

Case 1:
If nYears <= 0 Then
'funCalcAge = "Less Than Year"
funCalcAge = " 0 yo"
Else
funCalcAge = " " & nYears & " yo"
End If

Replace it with this...

Case 1
If nYears <= 0 Then
funCalcAge = " 0 yo"
ElseIf nYears > 10 Then
funCalcAge = "X"
Else
funCalcAge = " " & nYears & " yo"
End If

 

[Bob]

Absolutely Brilliant Thanx for the Help...Steve

 

[Bob]

Steve how can I add to this:
=IIf([tbOverDueAmount]>1,"Overdue Amount:",Null)

<1,"Credit"
Thanks Bob