DateDiff error?

[Guest]

I have been able to get the persons age based upon their DOB and the date
4/1/2006. I did all the calculations in different textbocs but I am trying
to do it in one line of code. Their DOB is on frmlicense in lbldob. I am
trying to move it over onto frmEntry in txtage. Here is the code on
frmlicense:

Forms!frmEntry!txtage = Int(DateDiff("m", (DateValue(Mid$([lbldob], 5, 2) &
"/" & Right([lbldob], 2) & "/" & Left([lbldob], 4))), #4/1/2006#) / 12)

It tells me object does not support this property or method. Originally, I
transfered the DOB onto a textbox in frmEntry, then had another textbox turn
it into a datevalue. Then once the datevalue was gotten it got put into a
textbox, which did the calculation for the age. It worked that way just
fine, but I wanted to put it into just one line of code. What did I do wrong?

 

[Dirk Goldgar]

I have been able to get the persons age based upon their DOB and the
date 4/1/2006. I did all the calculations in different textbocs but
I am trying to do it in one line of code. Their DOB is on frmlicense
in lbldob. I am trying to move it over onto frmEntry in txtage.
Here is the code on frmlicense:

Forms!frmEntry!txtage = Int(DateDiff("m", (DateValue(Mid$([lbldob],
5, 2) & "/" & Right([lbldob], 2) & "/" & Left([lbldob], 4))),
#4/1/2006#) / 12)

It tells me object does not support this property or method.
Originally, I transfered the DOB onto a textbox in frmEntry, then had
another textbox turn it into a datevalue. Then once the datevalue
was gotten it got put into a textbox, which did the calculation for
the age. It worked that way just fine, but I wanted to put it into
just one line of code. What did I do wrong?

What kind of control is "lbldob"? The "lbl" prefix usually indicates
that the object is a label control, but you're using it as if it had a
value like a text box. If it really is a label, then you'd need to
refer to its Caption property to get the text that the label displays.

Your formula for age is not quite right, I think. Bear in mind that the
difference between even #3/31/2006# and #4/1/2006# will be returned as 1
month, as far as DateDiff is concerned, because DateDiff counts month
*boundaries*, not whole months. See this web page for a variety of ways
to calculate age:

http://www.mvps.org/access/datetime/date0001.htm

 

[Guest]

Well, I decided to just go on doing it the way it was. Because I can't
figure it out. Yes, lbldob is a label on frmlicense that the DOB info is put
into after it is found.

 

[Dirk Goldgar]

Well, I decided to just go on doing it the way it was. Because I
can't figure it out. Yes, lbldob is a label on frmlicense that the
DOB info is put into after it is found.

Here, try this:

Dim dtDOB As Date
Dim dtAsOf As Date

dtAsOf = #4/1/2006#

dtDOB = CDate( _
Mid$(Me!lbldob.Caption, 5, 2) & "/" & _
Right$(Me!lbldob.Caption, 2) & "/" & _
Left$(Me!lbldob.Caption, 4) _
)

Forms!frmEntry!txtage = _
DateDiff("yyyy", dtDOB, dtAsOf) + _
Int( Format(dtAsOf, "mmdd") < Format( dtDOB, "mmdd") )

 

[Douglas J Steele]

Sorry to argue, Dirk, but not everyone uses mm/dd/yyyy as their short date
format, and CDate is one of the (few) date functions in Access that actually
respects the user settings.

For that reason, I'd suggest using DateSerial, rather than CDate. Assuming
that lbldob.caption contains the date of birth in yyyymmdd format, that
would be:

dtDOB = DateSerial( _
Left$(Me!lbldob.Caption, 4), _
Mid$(Me!lbldob.Caption, 5, 2), _
Right$(Me!lbldob.Caption, 2)
)

I believe, however that CDate will correct translate a date in yyyy-mm-dd
format, regardless of what the regional settings are, so another alternative
would be:

dtDOB = CDate( _
Format(Me!lbldob.Caption, "####-##-##")
)

 

[Dirk Goldgar]

Sorry to argue, Dirk, but not everyone uses mm/dd/yyyy as their short
date format, and CDate is one of the (few) date functions in Access
that actually respects the user settings.

For that reason, I'd suggest using DateSerial, rather than CDate.
Assuming that lbldob.caption contains the date of birth in yyyymmdd
format, that would be:

dtDOB = DateSerial( _
Left$(Me!lbldob.Caption, 4), _
Mid$(Me!lbldob.Caption, 5, 2), _
Right$(Me!lbldob.Caption, 2)
)

I believe, however that CDate will correct translate a date in
yyyy-mm-dd format, regardless of what the regional settings are, so
another alternative would be:

dtDOB = CDate( _
Format(Me!lbldob.Caption, "####-##-##")
)

Good points, Doug. I like the compactness of your second suggestion,
though I'd expect the first one to be more efficient in terms of
execution speed -- not that it matters, and I'm just guessing.