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)

 

[Al Campagna]

Bob,
Just...
= "2yo"
would be all you need to find records where the Age contained "2yo"

It would be better if you showed us sample data entries in your age
field, and how you want to filter against that field.
(ex. This is what I have for sample data... and this is what I want to see
my criteria deliver.)

You should consider removing the "yo" from your entries. If the field
were called AgeInYears, you could make that field numeric... not text.
Adding text to a number will cause more work down the road... say... when
you try to find horses older than 2 and younger than 4. That text "yo" will
have to be removed from the string, before any numeric logic is applied.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your life."

 

[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]