Last to figures Only

  • Thread starter Thread starter Bob Vance
  • Start date Start date
B

Bob Vance

At the moment when I use this formula tbName will get "2005 Mary" what I
wany is only the last to figures to be entered into tbName like "05 Mary"
Me.tbName.value = Format(cbDateOfBirth.value, "00") & " " _
& Me.CbMotherName.value
 
At the moment when I use this formula tbName will get "2005 Mary" what I
wany is only the last to figures to be entered into tbName like "05 Mary"
Me.tbName.value = Format(cbDateOfBirth.value, "00") & " " _
                                   & Me.CbMotherName.value
Click to expand...

try this:
Format(cbDateOfBirth.Value, "yy") & " " & Me.CbMotherName.Value
Fred
 
try this:
Format(cbDateOfBirth.Value, "yy") & " " & Me.CbMotherName.Value
Fred
The trouble is that cbDateOfBirth is just a text field with a record source
2010;2011 etc...
Regards Bob
 
try this:
Format(cbDateOfBirth.Value, "yy") & " " & Me.CbMotherName.Value
Fred
The trouble is that cbDateOfBirth is just a text field with a record source
2010;2011 etc...
Regards Bob
Click to expand...

Sorry our telepathy didn't work to determine that fact... which you did not
state.

Use

Right(cbDateOfBirth, 2)

in that case, and hope that your data is reasonably clean (a cbDateOfBirth
value of "2005 in May or June" will give you a wrong answer).
 
Thanks John, Age is only determined by years and changes on aug
1st....Regards 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
 
John it turned 2007 to 7 not 07 , Thanks Bob

John W. Vinson said:
Sorry our telepathy didn't work to determine that fact... which you did
not
state.

Use

Right(cbDateOfBirth, 2)

in that case, and hope that your data is reasonably clean (a cbDateOfBirth
value of "2005 in May or June" will give you a wrong answer).
Click to expand...
 
Back
Top