How to convert a combo box with text to a date? 1

[Dophia]

I have been reading posts, but I can't find an answer to my problem.

I have a form which asks the age of a person.
One field is the number (eg. "1" month or "1" year)
Second field is a combo box which lists "weeks", "months" and "years".

So, you would enter a number in the first field and then chose from the combo box whether the age number is in weeks, months or years.

How can I convert the two fields to a date, which I can use then use as a "date of birth" to calculate their age as of today?

Any help would be appreciated.
Thanks,
Dophia

 

[dhookom]

You could only approximate age. Try code like:

Dim strInterval as String
Dim datDOB as Date 'date of birth
SELECT Case Me.cboDateInterval
  Case "Weeks"
     strInterval = "ww"
  Case "Months"
     strInterval = "m"
  Case "Years"
     strInterval = "yyyy"
End Select
datDOB = DateAdd(strInterval, -Me.txtNumber, Date)

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Dophia]

Duane: Thanks for your response. I can't get this code to do anything in my database. I revised your code to reflect my field names as follows:

Private Sub CboAge_Estimated_Mth_Yr_AfterUpdate()
Dim strInterval As String
Dim DOB As Date 'date of birth'
Select Case Me.CboAge_Estimated_Mth_Yr
Case "week(s)"
strInterval = "ww"
Case "month(s)"
strInterval = "m"
Case "year(s)"
strInterval = "yyyy"
End Select
DOB = DateAdd(strInterval, -Me.Age_Estimated, Date)

End Sub


Do you have any suggestions?
Thank you,
Dophia

 

[dhookom]

You have calculated the estimated DOB but you don't do anything with it. Do you want to store this in a field or display it on a form or report?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Dophia]

I would like to display it on a form and on a report.

 

[dhookom]

It would help if you had provided the field names, etc.
I would create a public function in a module.

Function GetEstDOB(strInterval as String, _
    intAgeEst as Integer) as Date
   Dim DOB As Date 'date of birth'
   Select Case strInterval
      Case "week(s)"
        strInterval = "ww"
      Case "month(s)"
        strInterval = "m"
      Case "year(s)"
        strInterval = "yyyy"
   End Select
   GetEstDOB = DateAdd(strInterval, -intAgeEst, Date)
End Function

You can then use this function in a form or report with a control source like:
=GetEstDOB([IntervalField], [AgeEstField])

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Dophia]

Thank you for your help and quick response! I'll try what you have provided.
Thanks,
Dophia

 

[TheAceMan1]

How are ya Dophia . . .

Whats wrong with simply asking the [blue]birthdate[/blue] and then breaking it down into days/weeks/months/years as necessary? . . . It seems your working against your own logic (what you desire can be input by the user) . . .

[blue]Your thoughts?[/blue]

See Ya! . . . . . .

 

[TheAceMan1]

Hit submitt too soon . . .

[blue]Consider how complexing it would be for you (or any user) to sit down an figure how many weeks old they are!?[/blue] . . . Ya Think?

See Ya! . . . . . .

 

[Dophia]

Actually, I am working on a database for an animal shelter and the age of stray animals are estimated. If we entered a DOB when we didn't know it, it would be confusing when we later determined that the animal was a different age. I also have a field which asks for the DOB, for instance when a surrendered animal is taken in, and the previous owner knows it. So, I have two age fields, one estimated and one known. Either one is entered. It is for the estimated age, that I want to calculate the "estimated" age since the animal could be in the shelter for months or years, unfortunately.

Do you have any suggestions?
Thanks for your input,

Dophia

 

[dhookom]

If you have only one estimated field, how are you storing both the number and interval?

One field is the number (eg. "1" month or "1" year)
Second field is a combo box which lists "weeks", "months" and "years".

Maybe you should tell us what your actual fields are so we know how your data is stored.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Dophia]

Sorry, I meant one field for the DOB and two fields for the estimated age....one for the number and one for the interval.

The fields are:
[Date_of_birth] which is a Date type formatted as mmm-dd-yy
[Age_Estimated] which is a number type (long interger)
[Age_Estimated_Mth_Yr] which is a text type

The [Age_Estimated_Mth_Yr] has a lookup:
Display Control: List box
Row Source Type: Value List
Row Source: week(s);month(s);year(s)

 

[dhookom]

Did you create the function GetEstDOB() as I suggested? This should return an estimated date of birth based on your two fields. If you want to convert this to a number of years and you want to trap errors for null values, use these two functions in a query or control source like:

[tt][blue]GetEstDOB([Age_Estimated_Mth_Yr],[age_estimated])[/blue][/tt]

[tt][blue]GetEstAge([Age_Estimated_Mth_Yr],[age_estimated])[/blue][/tt]

Function GetEstDOB(strInterval As Variant, _
    intAgeEst As Variant) As Variant
    If Not (IsNull(strInterval) Or IsNull(intAgeEst)) Then
        Select Case strInterval
           Case "week(s)"
             strInterval = "ww"
           Case "month(s)"
             strInterval = "m"
           Case "year(s)"
             strInterval = "yyyy"
        End Select
        GetEstDOB = DateAdd(strInterval, -intAgeEst, Date)
     Else
        GetEstDOB = Null
    End If
End Function

Function GetEstAge(strInterval As Variant, _
    intAgeEst As Variant) As Variant
    If Not (IsNull(strInterval) Or IsNull(intAgeEst)) Then
        Select Case strInterval
           Case "week(s)"
             strInterval = "ww"
           Case "month(s)"
             strInterval = "m"
           Case "year(s)"
             strInterval = "yyyy"
        End Select
        GetEstAge = (Date - DateAdd(strInterval, -intAgeEst, Date)) / 365.25
     Else
        GetEstAge = Null
    End If
End Function

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Dophia]

Duane: Thank you, Thank you, Thank you!! This works perfectly!

I've been working on this database for 4 months or more, on a volunteer basis for our local humane society. So, I greatly appreciate the work that you did to help me.


Thank you,
Dophia

 

[Dophia]

Duane; Sorry to bother you again, but this only works in the form that I created, and not in the query.
I have a query based on the table which has the fields [Date_of_birth] and [Age_estimated] and [Age_estimated_Mth_Yr].

I then added two new fields to the table:
[Age1] number type-long interger
[Age2] Date type- format mmm-dd-yy

Then I added the two fields to the QryDOB. The QryDOB has all the fields.


On the FrmDOB I put all the fields and in the new fields, I put as the Control Source, the function, as follows:
[Age1] has Control Source field '=GetEstAge([Age_Estimated_Mth_Yr],[age_estimated])'

[Age2] has Control Source field '=GetEstDOB([Age_Estimated_Mth_Yr],[age_estimated])'


So, it works perfectly on the form, but I don't know what to do to make it work in the QryDOB. Currently I have the two new fields in it, and when I use an expression to put "=GetEstDOB([Age_Estimated_Mth_Yr],[age_estimated])" , I get an error message "Undefined Function 'GetEstAge'in expression. I want to put the QryDOB on other forms and so it would be better if it worked on it's own.

Thank you for your time,
Dophia

 

[dhookom]

You columns/fields in the query would look like:
[tt][blue]
[Age1]: GetEstAge([Age_Estimated_Mth_Yr],[age_estimated])

[Age2]: GetEstDOB([Age_Estimated_Mth_Yr],[age_estimated])
[/blue][/tt]

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Dophia]

When I do what you suggested, I get an error as follows:

'[Age1]' is not a valid name. Make sure it does not include invalid characters or punction...."

Help!
Thanks, Dophia

 

[dhookom]

Sorry about my goof. Remove the []s from around Age1 and Age2.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Dophia]

Duane: I had tried that before and as I wrote earlier:

"...it works perfectly on the form, but I don't know what to do to make it work in the QryDOB. Currently I have the two new fields in it, and when I use an expression to put "=GetEstDOB([Age_Estimated_Mth_Yr],[age_estimated])" , I get an error message "Undefined Function 'GetEstAge'in expression. I want to put the QryDOB on other forms and so it would be better if it worked on it's own."

Thanks, Dophia

 

[PHV]

Did you write the functions in a standard code module instead of the form's class module ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886