Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Slight Script change!

Discussion in 'Information Technology' started by Bob, Jul 28, 2006.

  1. Bob

    Bob
    Expand Collapse
    Guest

    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
     
  2. Loading...

    Similar Threads Forum Date
    A slightly absurd question Questions and Answers Jun 13, 2014
    Slight Confusion Questions and Answers Dec 30, 2012
    Sikh News Mercury rises slightly in Punjab (New Kerala) Breaking News Jan 4, 2008
    General On Scriptures Blogs Sep 27, 2016
    Which "Three Inscriptions" Are Recorded? Intellectual Translations by SPNers Oct 11, 2015

  3. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    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

    Bob wrote:
    > 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
    >
    >
     
  4. Bob

    Bob
    Expand Collapse
    Guest

    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

    "Steve Schapel" <schapel@mvps.org.ns> wrote in message
    news:OrdBFk9nGHA.4332@TK2MSFTNGP03.phx.gbl...
    > 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
    >
    > Bob wrote:
    >> 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
     
  5. Bob

    Bob
    Expand Collapse
    Guest

    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
     
  6. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    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

    --
    Steve Schapel, Microsoft Access MVP

    Bob wrote:
    > 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
    >
    >
     
  7. Bob

    Bob
    Expand Collapse
    Guest

    Absolutely Brilliant :) Thanx for the Help...Steve

    "Steve Schapel" <schapel@mvps.org.ns> wrote in message
    news:%23NWFIb%23nGHA.3504@TK2MSFTNGP02.phx.gbl...
    > 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
    >
    > --
    > Steve Schapel, Microsoft Access MVP
    >
    > Bob wrote:
    >> 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
     
  8. Bob

    Bob
    Expand Collapse
    Guest

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

    <1,"Credit"
    Thanks Bob

    "Bob" <xxx@xx.xx> wrote in message news:e8feuf$rk3$1@lust.ihug.co.nz...
    > Absolutely Brilliant :) Thanx for the Help...Steve
    >
    > "Steve Schapel" <schapel@mvps.org.ns> wrote in message
    > news:%23NWFIb%23nGHA.3504@TK2MSFTNGP02.phx.gbl...
    >> 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
    >>
    >> --
    >> Steve Schapel, Microsoft Access MVP
    >>
    >> Bob wrote:
    >>> 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

    >
    >
     

Share This Page