Why do I receive an error when trying to change a data type?

[Guest]

I had a question on a Access problem. I am trying to change the data type
from number to short date on a table. I ran a make table query and have my
table, when I change the data type in the tables design view I receive this
error:


Access encountered errors while converting the data. The contents of fields
in 9850 records will be deleted. Would you like to proceed?

 

[Bill Mosca, MS Access MVP]

When Access can't figure out how to convert data from one type to another,
it just skips it. I'd say your numbers are not compatable with dates.

If you do not need to save the original numbers, go ahead and let the
conversion take place. If you do need to save them, create another field,
update it to what is in the numeric field and then do your conversion.

 

[Guest]

Try testing the numbers that you are attempting to convert, using the IsDate
function:

SELECT IsDate([FieldName]) AS [Invalid Date]
FROM TableName
WHERE IsDate([FieldName])=0;

where FieldName is the name of your field, and TableName is the name of your
table. Make the appropriate substitutions. This query should not return any
records if the numbers can be correctly coerced into valid dates.


Tom Wickerath
Microsoft Access MVP

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

 

[Guest]

Oops. Try this instead:

SELECT IsDate (CVDate([FieldName])) AS [Invalid Date]
FROM TableName
WHERE IsDate (CVDate([FieldName]))=0;


Tom Wickerath
Microsoft Access MVP

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

Try testing the numbers that you are attempting to convert, using the IsDate
function:

SELECT IsDate([FieldName]) AS [Invalid Date]
FROM TableName
WHERE IsDate([FieldName])=0;

where FieldName is the name of your field, and TableName is the name of your
table. Make the appropriate substitutions. This query should not return any
records if the numbers can be correctly coerced into valid dates.


Tom Wickerath
Microsoft Access MVP

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

I had a question on a Access problem. I am trying to change the data type
from number to short date on a table. I ran a make table query and have my
table, when I change the data type in the tables design view I receive this
error:


Access encountered errors while converting the data. The contents of fields
in 9850 records will be deleted. Would you like to proceed?