Bad dates.

[Guest]

I have a table of data that I imported in from a text file. All of the
fields are set as text. One of the fields is a date of birth field and I
think that because it's stored as text my query results are incorrect.

I'm trying to select the oldest date of birth for a particular phone number
and it's not selecting the correct one. I tried to switch the field type to
date/time and I received an error message stating that 113 of the dates were
not converted due to some error.

I'm not sure of how to proceed. I tried using the dateval function and
received the same error. Any ideas of 1.) How I can figure out what it is
about the bad dates that's causing this conversion error and 2.) How I can
convert them to date/time values?

 

[Guest]

Hi Jim,

First, make a back-up copy of your database before doing anything else.

I would try adding a new field to your table, as a Date/Time datatype. Then
run an update query to update the values. Something like this:

Field: MyNewDateField
UpdateTo: CDate([MyTextDateField])

Examing the results afterwards for the 113 records that fail to convert. You
might be able to easily spot something common in this data that is causing
the problem. Also, this article is worth checking out:

International Dates in Access
http://allenbrowne.com/ser-36.html


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[Guest]

Thanks Tom! That enabled me to see what the problem is. The dates are
incorrect because they are for February 29 when the year is not a leap year.
Now I just need a way to identify all of these dates in a query. Any ideas?

Hi Jim,

First, make a back-up copy of your database before doing anything else.

I would try adding a new field to your table, as a Date/Time datatype. Then
run an update query to update the values. Something like this:

Field: MyNewDateField
UpdateTo: CDate([MyTextDateField])

Examing the results afterwards for the 113 records that fail to convert. You
might be able to easily spot something common in this data that is causing
the problem. Also, this article is worth checking out:

International Dates in Access
http://allenbrowne.com/ser-36.html


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

I have a table of data that I imported in from a text file. All of the
fields are set as text. One of the fields is a date of birth field and I
think that because it's stored as text my query results are incorrect.

I'm trying to select the oldest date of birth for a particular phone number
and it's not selecting the correct one. I tried to switch the field type to
date/time and I received an error message stating that 113 of the dates were
not converted due to some error.

I'm not sure of how to proceed. I tried using the dateval function and
received the same error. Any ideas of 1.) How I can figure out what it is
about the bad dates that's causing this conversion error and 2.) How I can
convert them to date/time values?

 

[Guest]

Hi Jim,

Sure. Just run a simple test. Something like this:

SELECT TableName.MyDate, IsDate([MyDate]) AS [Is Date]
FROM TableName;

Values shown as 0 are not valid dates.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[John W. Vinson]

Thanks Tom! That enabled me to see what the problem is. The dates are
incorrect because they are for February 29 when the year is not a leap year.
Now I just need a way to identify all of these dates in a query. Any ideas?

There is an IsDate() function which takes a string argument and
returns True if it's a valid date, False otherwise - might that help?

John W. Vinson [MVP]

 

[Guest]

Thank you Tom. That worked like a charm!

Hi Jim,

Sure. Just run a simple test. Something like this:

SELECT TableName.MyDate, IsDate([MyDate]) AS [Is Date]
FROM TableName;

Values shown as 0 are not valid dates.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Thanks Tom! That enabled me to see what the problem is. The dates are
incorrect because they are for February 29 when the year is not a leap year.
Now I just need a way to identify all of these dates in a query. Any ideas?

 

[Guest]

Thanks John. That worked great!

There is an IsDate() function which takes a string argument and
returns True if it's a valid date, False otherwise - might that help?

John W. Vinson [MVP]

 

[Guest]

You're welcome. Glad to hear that you got it working!


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________