Year calculation

[ppoole16]

what would the code be to have a user enter an age in a text box and have the db calculate and display year of birth +/- 5 years in a different textbox?

Basically the reverse of this and +/- 5 years:
())+(Date()<DateSerial(Year(Date()),Month([DOB]),Day([DOB])))

Thanks.

 

[Mile-O]

Wouldn't DateAdd("yyyy", -Age, [InitialDate]) do?

 

[ppoole16]

I get a #Name error.

 

[Mile-O]

Well, you'll need to change Age to your textbox where you entef the age and change [InitialDate] to either today's Date() or the control that contains the date you want to calculate back from.

 

[ppoole16]

I'm close, but how do I get it to calculate a range of +/- 5 years and to only display the years? Date() will show today's month, day, and year.

 

[Mile-O]

Where are you wanting the output?

If it's just the year you want, then you could simply use:

Dim intCounter As Integer

For intCounter = -5 To 5
    Debug.Print (Year(Date())-Age) + intCounter
Next intCounter

That routine will print your -5 to +5 years in the immediate window.

 

[ppoole16]

I'd like the output to a txtbox called [yob]. I'm using it in a search form and I need the user to enter an age in one txtbox, have a range of years display in another box, and then search my table for records with a year matching one of calculated years. I think I've got the search down, it's just the calculation. so how do I refine your last code to display the range of years in [yob]?

And where does that code go? After Update?

-Phil

 

[Mile-O]

You''ll somehow have to work out how to break up the list to use as criteria in a query though.

Dim intCounter As Integer, strList As String

For intCounter = -5 To 5
    Me.YOB = strList & (Year(Date())-Age) + intCounter & " "
Next intCounter

Me.YOB = Trim(strList)

 

[ppoole16]

Here's what I have. The user enters the age in the [txtYear] box.

Dim intCounter As Integer, strList As String

For intCounter = -5 To 5
Me.YOB = strList & (Year(Date) - [txtYear]) + intCounter & " "
Next intCounter

Me.YOB = Trim(strList)

Where should the code go? I can't get it to display in YOB.

 

[Mile-O]

If you are going to use txt for one textbox try and keep using the standard throughout your database.

Private Sub txtYear_AfterUpdate()

    Dim intCounter As Integer, strList As String 

    If IsNull(Me.txtYear) Then Exit Sub

    For intCounter = -5 To 5 
        Me.[b]txt[/b]YOB = strList & (Year(Date) - Me.txtYear) + intCounter & " " 
    Next intCounter 

    Me.[b]txt[/b]YOB = Trim(strList)

End Sub

 

[ppoole16]

I don't have a clue. I've cut and pasted and added txt to the name of YOB to give txtYOB. When I put an age in txtYear, nothing happens.

Does it make a difference that neither box is bound?

 

[ppoole16]

Here's an example of what I have. Hopefully you can see what I'm doing wrong.

Thanks for all the time you've given me.

 

[Mile-O]

Sorry, mistake in my code (it's late here):

Me.txtYOB = strList & (Year(Date) - Me.txtYear) + intCounter & " "

should be:

strList = strList & (Year(Date) - Me.txtYear) + intCounter & " "

 

[ppoole16]

It works great. Thanks. I'll name my first born after you (I'm not sure the wife will go for that though.)

 

[Mile-O]

lol, no probs. Got there in the end; that's the main thing.