Converting Text Date to Real Date to #

[sross81]

Hello,
In the database that I use which is an out of the box system the date of
birth is stored as text. I want to be able to have a field that calculates a
persons age based on the current date and then filters the age to only
include patients in a certain age range. I can get the calculation to occur
and it looks right but when I filter it I still get a datatype mismatch error
so I don't think its completely converting the age to a # although it looks
like a number. Here is what I have in my Access query to convert it.

Age:
CInt(DateDiff("yyyy",CVDate(Format([date_of_birth],"0000\/00\/00")),(Now())))

Then in another query I use the query that does the calculation and I filter
age in the criteria saying >=52 and <=69 but it errors out.

What am I doing wrong??

 

[sross81]

I just wanted to add although the DOB is stored as text it looks like this
for example
20090824 but its just stored as text.

 

[Jerry Whittle]

What happens when you omit the criteria?

Possibly this criteria would work better:
Between 52 and 69

 

[sross81]

It runs and it just shows an age value which appears to be a number. It only
has the problem if I try to filter it. I have tried using between and the >=
signs.

What happens when you omit the criteria?

Possibly this criteria would work better:
Between 52 and 69
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hello,
In the database that I use which is an out of the box system the date of
birth is stored as text. I want to be able to have a field that calculates a
persons age based on the current date and then filters the age to only
include patients in a certain age range. I can get the calculation to occur
and it looks right but when I filter it I still get a datatype mismatch error
so I don't think its completely converting the age to a # although it looks
like a number. Here is what I have in my Access query to convert it.

Age:
CInt(DateDiff("yyyy",CVDate(Format([date_of_birth],"0000\/00\/00")),(Now())))

Then in another query I use the query that does the calculation and I filter
age in the criteria saying >=52 and <=69 but it errors out.

What am I doing wrong??

 

[Dale Fye]

I'm not familiar with the CVDate function, and it does not appear to work in
Access 2007. Have you tried cDate()

Using the DateDiff function and passing it two dates will not give you a
very accurate representation of the age in years. As an example, try:

?dateDiff("yyyy", #12/30/2008#, #1/2/2009#)

Even though these two dates are only a couple of days apart, the DateDiff
function evaluates the difference between the years of the two dates, and
returns 1. A better reprepsentation is along the lines of:

Age = DateDiff("yyyy", [DOB], Date()) _
+ (Format(Date(), "mmdd") < Format([DOB], "mmdd")

This will compute the difference in years and will then subtract 1 if the
month/day of today is < the monthday of the [DoB]

Rather than put all of that in a query, I think I'd probably write a
function and call the function in the query. Doing it this way is much more
readable.

Public Function fnAge(DoB as Variant) as Variant

Dim dtDoB as date

'Check for NULL
if isnull(DoB) then
fnAge = NULL
Else
dtDOB= cdate(format(DoB, "0000\/00\/00))
Age = DateDiff("yyyy", dtDOB, Date()) _
+ (Format(Date(), "mmdd") < Format(dtDOB, "mmdd")
End If

End Function

----
HTH
Dale

I just wanted to add although the DOB is stored as text it looks like this
for example
20090824 but its just stored as text.

Hello,
In the database that I use which is an out of the box system the date of
birth is stored as text. I want to be able to have a field that calculates a
persons age based on the current date and then filters the age to only
include patients in a certain age range. I can get the calculation to occur
and it looks right but when I filter it I still get a datatype mismatch error
so I don't think its completely converting the age to a # although it looks
like a number. Here is what I have in my Access query to convert it.

Age:
CInt(DateDiff("yyyy",CVDate(Format([date_of_birth],"0000\/00\/00")),(Now())))

Then in another query I use the query that does the calculation and I filter
age in the criteria saying >=52 and <=69 but it errors out.

What am I doing wrong??

 

[sross81]

I tried this statement you wrote below just to test it out and it says
invalid syntax

Age = DateDiff("yyyy", [DOB], Date()) _

+ (Format(Date(), "mmdd") < Format([DOB], "mmdd")

I have never created a function. I assume I do this through VBA. Is there
anyplace you can direct me to get started in creating a function, saving it,
and be able to call it from within the query?

I'm not familiar with the CVDate function, and it does not appear to work in
Access 2007. Have you tried cDate()

Using the DateDiff function and passing it two dates will not give you a
very accurate representation of the age in years. As an example, try:

?dateDiff("yyyy", #12/30/2008#, #1/2/2009#)

Even though these two dates are only a couple of days apart, the DateDiff
function evaluates the difference between the years of the two dates, and
returns 1. A better reprepsentation is along the lines of:

Age = DateDiff("yyyy", [DOB], Date()) _
+ (Format(Date(), "mmdd") < Format([DOB], "mmdd")

This will compute the difference in years and will then subtract 1 if the
month/day of today is < the monthday of the [DoB]

Rather than put all of that in a query, I think I'd probably write a
function and call the function in the query. Doing it this way is much more
readable.

Public Function fnAge(DoB as Variant) as Variant

Dim dtDoB as date

'Check for NULL
if isnull(DoB) then
fnAge = NULL
Else
dtDOB= cdate(format(DoB, "0000\/00\/00))
Age = DateDiff("yyyy", dtDOB, Date()) _
+ (Format(Date(), "mmdd") < Format(dtDOB, "mmdd")
End If

End Function

----
HTH
Dale

I just wanted to add although the DOB is stored as text it looks like this
for example
20090824 but its just stored as text.

Hello,
In the database that I use which is an out of the box system the date of
birth is stored as text. I want to be able to have a field that calculates a
persons age based on the current date and then filters the age to only
include patients in a certain age range. I can get the calculation to occur
and it looks right but when I filter it I still get a datatype mismatch error
so I don't think its completely converting the age to a # although it looks
like a number. Here is what I have in my Access query to convert it.

Age:
CInt(DateDiff("yyyy",CVDate(Format([date_of_birth],"0000\/00\/00")),(Now())))

Then in another query I use the query that does the calculation and I filter
age in the criteria saying >=52 and <=69 but it errors out.

What am I doing wrong??

 

[sross81]

I figured out how to add a module and this is what I put in

Public Function fnAge(date_of_birth As Variant) As Variant

Dim dtDoB As Date

'Check for NULL
If IsNull(date_of_birth) Then
fnAge = Null
Else
dtDOB= cdate(format(date_of_birth, "0000\/00\/00))
Age = DateDiff("yyyy", dtDOB, Date()) _
+ (Format(Date(), "mmdd") < Format(dtDOB, "mmdd")
End If

End Function


I only changed DOB to date_of_birth since that is what the column is called
in my DB but I get syntax errors.

I tried this statement you wrote below just to test it out and it says
invalid syntax

Age = DateDiff("yyyy", [DOB], Date()) _

+ (Format(Date(), "mmdd") < Format([DOB], "mmdd")

I have never created a function. I assume I do this through VBA. Is there
anyplace you can direct me to get started in creating a function, saving it,
and be able to call it from within the query?

I'm not familiar with the CVDate function, and it does not appear to work in
Access 2007. Have you tried cDate()

Using the DateDiff function and passing it two dates will not give you a
very accurate representation of the age in years. As an example, try:

?dateDiff("yyyy", #12/30/2008#, #1/2/2009#)

Even though these two dates are only a couple of days apart, the DateDiff
function evaluates the difference between the years of the two dates, and
returns 1. A better reprepsentation is along the lines of:

Age = DateDiff("yyyy", [DOB], Date()) _
+ (Format(Date(), "mmdd") < Format([DOB], "mmdd")

This will compute the difference in years and will then subtract 1 if the
month/day of today is < the monthday of the [DoB]

Rather than put all of that in a query, I think I'd probably write a
function and call the function in the query. Doing it this way is much more
readable.

Public Function fnAge(DoB as Variant) as Variant

Dim dtDoB as date

'Check for NULL
if isnull(DoB) then
fnAge = NULL
Else
dtDOB= cdate(format(DoB, "0000\/00\/00))
Age = DateDiff("yyyy", dtDOB, Date()) _
+ (Format(Date(), "mmdd") < Format(dtDOB, "mmdd")
End If

End Function

----
HTH
Dale

I just wanted to add although the DOB is stored as text it looks like this
for example
20090824 but its just stored as text.

:

Hello,
In the database that I use which is an out of the box system the date of
birth is stored as text. I want to be able to have a field that calculates a
persons age based on the current date and then filters the age to only
include patients in a certain age range. I can get the calculation to occur
and it looks right but when I filter it I still get a datatype mismatch error
so I don't think its completely converting the age to a # although it looks
like a number. Here is what I have in my Access query to convert it.

Age:
CInt(DateDiff("yyyy",CVDate(Format([date_of_birth],"0000\/00\/00")),(Now())))

Then in another query I use the query that does the calculation and I filter
age in the criteria saying >=52 and <=69 but it errors out.

What am I doing wrong??

 

[Jerry Whittle]

By filter do you mean using the filter tools instead of using criteria in
queries? I've had problems using the filter tools on complex queries. As a
rule, I use parameter queries to better define what I want returned.

Also if you are using CDate, it will bomb out if any record returns
something that can't be evaluated as a date. It might even take a while to
bomb out. Try running IsDate on your date field and see if there are any
problem records.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

It runs and it just shows an age value which appears to be a number. It only
has the problem if I try to filter it. I have tried using between and the >=
signs.

What happens when you omit the criteria?

Possibly this criteria would work better:
Between 52 and 69
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hello,
In the database that I use which is an out of the box system the date of
birth is stored as text. I want to be able to have a field that calculates a
persons age based on the current date and then filters the age to only
include patients in a certain age range. I can get the calculation to occur
and it looks right but when I filter it I still get a datatype mismatch error
so I don't think its completely converting the age to a # although it looks
like a number. Here is what I have in my Access query to convert it.

Age:
CInt(DateDiff("yyyy",CVDate(Format([date_of_birth],"0000\/00\/00")),(Now())))

Then in another query I use the query that does the calculation and I filter
age in the criteria saying >=52 and <=69 but it errors out.

What am I doing wrong??

 

[John Spencer]

I suspect that you have a problem with one or more of the text values not
converting and generating an error. I would use something like the following
to convert the value to a date and avoid errors if the Date_Of_Birth field
should be blank or a null value or otherwise incapable of being converted.

IIF(IsDate(Format(Date_of_birth,"@@@@-@@-@@")),CDate(Format(Date_of_birth,"@@@@-@@-@@")),Null)

If you use Dale Fye's function idea, I would test the input to see if it could
be read as a date. To handle your specific situation the function might look
like:

Public Function fGetAge(strIN) as variant
Dim dtDate as Date

If IsDate(Format(Date_of_birth,"@@@@-@@-@@")) = False then
fGetAge = Null
Else
dtDate = CDate(Format(Date_of_birth,"@@@@-@@-@@"))
fGetAge = DateDiff("yyyy", dtDate, Date()) _
+ (Format(Date(), "mmdd") < Format(dtDate, "mmdd")
End if

End Function

DO NOT Make changes to the function. In a query, you would have something
like the following as a calculated field.

Field: Age: fGetAge(Date_of_Birth)

Another possibility is not to bother converting the ages at all and using
criteria to filter directly on the date of birth. If you need only one group
of ages at a time this might be more efficient than anything else.

Date_of_Birth Between Format(DateAdd("yyyy",69,Date()),"yyyymmdd") and
Format(DateAdd("yyyy",52,Date()),"yyyymmdd")

That should return all records where the age is between 52 and 69 as of
today's date. OF course, if you need to work with the ages separately this
solution does not work for you.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County