Change in data type

[Nicawette]

Hi all,

I've a problem when I change the data type;
I've a table (+- 1'000'000,- records) where all the field are in text.

I want to change the data type in the field "amount" (sample : 7502.15)
from text to number but when I do that I've an error message saying
that " access encountered errors while converting the data) the content
of fields in +- 1'000'000,- records were deleted".

Do you know how to change the filed type without deleting my data ?

PS I want to do that because pivot table in excel will be based on this
DB and if the field "amount" is in text I can't sum all the data.

Thank you in advance.

Nicolas

 

[Douglas J. Steele]

How are you attempting to change the data type?

As an interim step, you could always create a query that uses the CSng, CDbl
or CCur function on your text fields to convert them, then use that query
rather than the table to create the pivot table.

 

[Guest]

I would suggest adding a new field datatype Number, Single. Run an update
query updating the new field using data from the old text field. Then see
what does not convert and analyze to see why. Maybe some extra characters
like 2500+ or >3500.

 

[Guest]

Hi Nicolas,

One or more of your entries is likely text that cannot be converted to a
number. Try running the following query to discover the bad records:

SELECT [YourTableName].[YourFieldName]
FROM [YourTableName]
WHERE IsNumeric([YourFieldName])=0;

where you substitute YourTableName with the actual name of your table, and
YourFieldName with the actual name of your field (Amount, I believe). To use
this SQL statement, create a new query. Dismiss the Add Tables dialog without
adding any tables. In query design view, click on View > SQL View. You should
see the word SELECT highlighted. Copy the SQL statement shown above (Ctrl C)
and paste it into the SQL view (Ctrl V), replacing the SELECT keyword. You
can then switch back to the more familiar design view, if you wish, by
clicking on View > Design View. Run the query. If it returns any records,
then you need to make the appropriate correction.


Tom Wickerath
Microsoft Access MVP

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

 

[Nicawette]

Thank you all for your swift answers

nicolas

Tom Wickerath a écrit :

Hi Nicolas,

One or more of your entries is likely text that cannot be converted to a
number. Try running the following query to discover the bad records:

SELECT [YourTableName].[YourFieldName]
FROM [YourTableName]
WHERE IsNumeric([YourFieldName])=0;

where you substitute YourTableName with the actual name of your table, and
YourFieldName with the actual name of your field (Amount, I believe). To use
this SQL statement, create a new query. Dismiss the Add Tables dialog without
adding any tables. In query design view, click on View > SQL View. You should
see the word SELECT highlighted. Copy the SQL statement shown above (CtrlC)
and paste it into the SQL view (Ctrl V), replacing the SELECT keyword. You
can then switch back to the more familiar design view, if you wish, by
clicking on View > Design View. Run the query. If it returns any records,
then you need to make the appropriate correction.


Tom Wickerath
Microsoft Access MVP

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

Hi all,

I've a problem when I change the data type;
I've a table (+- 1'000'000,- records) where all the field are in text.

I want to change the data type in the field "amount" (sample : 7502.15)
from text to number but when I do that I've an error message saying
that " access encountered errors while converting the data) the content
of fields in +- 1'000'000,- records were deleted".

Do you know how to change the filed type without deleting my data ?

PS I want to do that because pivot table in excel will be based on this
DB and if the field "amount" is in text I can't sum all the data.

Thank you in advance.

Nicolas