Expression Critea!

[Bob V]

Is it possible to get a result on [Like "2yo"] this contains horses ages and
want to get a query showing horses that are "2yo"
Tahnks for any help...............Bob

Age: funCalcAge(Format('01-Aug-' &
[DateOfBirth],'dd/mmm/yyyy'),Format(Now(),'dd/mmm/yyyy'),1)

 

[John W. Vinson]

Is it possible to get a result on [Like "2yo"] this contains horses ages and
want to get a query showing horses that are "2yo"
Tahnks for any help...............Bob

Age: funCalcAge(Format('01-Aug-' &
[DateOfBirth],'dd/mmm/yyyy'),Format(Now(),'dd/mmm/yyyy'),1)

What's "funCalcAge"? What is the meaning of passing it (e.g.)

01-Aug-05/16/2005

as a string argument!? What is the algorithm for determining a horse's "age"?

John W. Vinson [MVP]

 

[Bob V]

John in the Southern hemisphere Race horses have one birthday to change
there age 1 Aug, Up where you are horses change there birthday 1 Jan. the
crazy thing was if a horse was born Jull 30 he would have turn 1 the next
day, bit now things have changed stallions can serve mares from 1 sep and
does not matter what date there born they turn 1 next year Aug
1st.....Thanks Bob
funCalcAge(Format(Nz(recInvoice_ItMdt.Fields("DateOfBirth"), 0) _
, "dd-mmm-yyyy"), Format("01-Aug-" & Year(Now()),
"dd-mmm-yyyy"), 1)

Is it possible to get a result on [Like "2yo"] this contains horses ages
and
want to get a query showing horses that are "2yo"
Tahnks for any help...............Bob

Age: funCalcAge(Format('01-Aug-' &
[DateOfBirth],'dd/mmm/yyyy'),Format(Now(),'dd/mmm/yyyy'),1)

What's "funCalcAge"? What is the meaning of passing it (e.g.)

01-Aug-05/16/2005

as a string argument!? What is the algorithm for determining a horse's
"age"?

John W. Vinson [MVP]

 

[Bob V]

Sorry here it is.......BTW ="2yo" didnt work .....Thanks Bob
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 = "0yo"

ElseIf nYears > 30 Then
funCalcAge = "X"
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

Is it possible to get a result on [Like "2yo"] this contains horses ages
and
want to get a query showing horses that are "2yo"
Tahnks for any help...............Bob

Age: funCalcAge(Format('01-Aug-' &
[DateOfBirth],'dd/mmm/yyyy'),Format(Now(),'dd/mmm/yyyy'),1)

What's "funCalcAge"? What is the meaning of passing it (e.g.)

01-Aug-05/16/2005

as a string argument!? What is the algorithm for determining a horse's
"age"?

John W. Vinson [MVP]