Between/And

[JPR]

Hello,

I have created a form with two unbound textboxes.

txtDOB in which i type a date of birth (mm/dd/yyyy age format)
txtRetire in which I would like to add a text via code, depending on the age range entered in txtDOB.

Examples: If me.txtdob between 01/01/1950 and 12/31/1950 then me.txtretire = "Age 70"

Thanks for any help.
I

 

[Mark_]

How do you determine retirement age?
Is this a hard coded range or are you looking the date up from a table?

 

[JPR]

Hello,

No, I will not pick up the data from a table.
Since it's a simple projection, I would like to write this estimated retirement age via code.
I do not expect to write more than 10 lines each of which will refer to a year.

Example:

if me.txtdob between 01/01/1951 and 12/31/1951 then me.txtretire = "Age 66"
If me.txtdob between 01/01/1953 and 12/31/1953 then me.txtretire = "Age 67"
If me.txtdob between 01/01/1950 and 12/31/1950 then me.txtretire = "Age 70"

Thank you

 

[arnelgp]

copy this code to a Module:

Public Function fnAgeYears(ByVal dteStart As Variant, Optional ByVal dteEnd As Variant = 0) As Integer
  ' Comments: Returns the age in years
  ' Params  : datBirthDate    Date to check
  ' Returns : Number of years
  ' Source  : Total Visual SourceBook

  On Error GoTo Proc_Err

  Dim intYears As Integer
 
    If IsDate(dteStart) = False Then
        Exit Function
    End If
    If IsNull(dteEnd) Or dteEnd = 0 Then
        dteEnd = Date
    End If
    intYears = Year(dteEnd) - Year(dteStart)
 
    If DateSerial(Year(dteEnd), Month(dteStart), Day(dteStart)) > dteEnd Then
        ' Subtract a year if birthday hasn't arrived this year
        intYears = intYears - 1
    End If
 
    fnAgeYears = intYears

Proc_Exit:
  Exit Function

Proc_Err:
  MsgBox "Error: " & Err.Number & ". " & Err.Description, , "modDateTime.AgeYears"
  Resume Proc_Exit
End Function

No, I will not pick up the data from a table.

add Code to each txtbox (txtDOB )aAfter Update event:

Private Sub txtDOB_AfterUpdate()
Me.txtRetire = "Age " & fnAgeYears(Me.txtDOB) - 7
End Sub

//Editedf AfterUpdate event

 

[Mark_]

Access does not use Between in VBA. You can use it in SQL though.
If you need different ranges than what ArnelGP has posted, you would need to do an IF / ElseIf / Else structure as your original data doesn't make a lot of sense.

 

[arnelgp]

or you can use this function:

Public Function fnAgeYears(ByVal dte As Variant) As String

Select Case True
Case dte >= #1/1/1950# And dte <= #12/31/1950#
    fnAgeYears = "Age 70"
Case dte >= #1/1/1951# And dte <= #12/31/1951#
    fnAgeYears = "Age 66"
Case dte >= #1/1/1953# And dte <= #12/31/1953#
    fnAgeYears = "Age 67"
End Select

End Function

Private Sub txtDOB_AfterUpdate()
Me.txtRetire = fnAgeYears(Me.txtDOB)
End Sub

 

[JPR]

This last code works great. Just was I was tryin to achieve.
Thank you. Appreciate your help

 

[Gasman]

Why are the age values incorrect?

 

[Pat Hartman]

Examples: If me.txtdob between 01/01/1950 and 12/31/1950 then me.txtretire = "Age 70"

Ok, call me crazy but this makes no sense. It is now 2024. so 2024 - 1950 = 74 So, is your calculation

Year(Date()) - Year(DOB) - 4 ?????

 

[JPR]

Hello Pat,

"Age 70" obviously is just an example of what I want to show up in the textbox. Thanks

 

[arnelgp]

k, call me crazy but this makes no sense.

not all calculations are Linear, if you can recall your Algebra or Trigo.

 

[DHookom]

I’m a bit OCDD (obsessive compulsive data driven) so I any solution that doesn’t require a small lookup table is a mistake. Data needs to be maintained in a table, not an expression or code.

 

[Mark_]

I’m a bit OCDD (obsessive compulsive data driven) so I any solution that doesn’t require a small lookup table is a mistake. Data needs to be maintained in a table, not an expression or code.

I'd second this. Also makes the query easy to do as OP can use Between, so long as his back end supports it! ☺

 

[Pat Hartman]

"Age 70" obviously is just an example of what I want to show up in the textbox. Thanks

Not so obvious when you provide sample data that does not compute. The function arnelgp provided takes into consideration the current date relative to the DOB and gives you the actual age of the person at the time the calculation occurs. That may in fact be what you want but it isn't what you asked for. I would think that if arnelgp's solution had been what you wanted, you might have just asked for "current age" without giving us bad examples where we were trying to figure out what your formula was to come up with the answer you provided.

 

[Mark_]

Not so obvious when you provide sample data that does not compute. The function arnelgp provided takes into consideration the current date relative to the DOB and gives you the actual age of the person at the time the calculation occurs. That may in fact be what you want but it isn't what you asked for. I would think that if arnelgp's solution had been what you wanted, you might have just asked for "current age" without giving us bad examples where we were trying to figure out what your formula was to come up with the answer you provided.

His data is actually worse than that.
Born in 50', retire at 70
Born in 51', retire at 66
Born in 53', retire at 67

Born in 52', doesn't get to retire?

 

[Isaac]

If cdate(me.txtdob.value) between #01/01/1950# and #12/31/1950# then
me.txtretire.value = "Age 70"
End if

 

[GPGeorge]

Here I go again.

Ambiguity is required in politics, poetry and humor, none of which have a place in database design.

 

[Isaac]

I always try to slyly introduce a bit of ambiguity into deadlines

 

[Mark_]

I always try to slyly introduce a bit of ambiguity into deadlines

"Padding", the term is "Padding". Trying not to be ambiguous!

 

[GPGeorge]

I always try to slyly introduce a bit of ambiguity into deadlines

Do you allow any degree of padding/ambiguity/slippage, etc. into your deadlines? Or is there a limit to how much ambiguity is too much.