Microsoft Access can't change the data type

[AlecMA]

Hi! I have a problem to build Pivot table in Excel 2003.
I’m creating this table base on the “External Data” which is a MS Access 2003 table.
The table has 50 fields and about 500.000 records. (This is the reason, I can’t just export table to Excel and then do pivoting). One of the fields of this table has a “text” type but stored numbers. Excel does not allowed me do Sum or Max function with this field – it needs to have Number data type.
I receiving this table “from outside”, so I can’t get the right data type from the beginning. If I’m trying simply opening the table, before, using as a data source for Pivot Table, in Design mode and just change data type from text to number, I have an error: "Microsoft Access can't change the data type. There isn't enough disk space or memory."
Any advice, how to change Data type in existing table using queries or something else what can help me to solve this problem.

Thank you very much at advance.

Alec.

 

[boblarson]

Why not connect to a query instead of the table and in the query you can convert the datatype. You don't have to use a table for a Pivot table in Excel, you can use a query from Access as well.

 

[AlecMA]

Bob,
Yes, I can use the query for pivoting, but the data type still going to be a "text"...

 

[boblarson]

Not if you create a column like this in the query:

WhateverYouWantToCallItHere:CDbl([YourFieldNameHere])

 

[AlecMA]

Thanks, Bob.
That may work.
Here is the solution I got from another forum and it works.

<A query-based way to solve it is craete another field in your table with the type you want, and then run this query :

update <YOURTABLENAME> set <YOURNEWFIELD> = val(<YOURNUMBERASTEXTFIELD>)

where <YOURTABLENAME> is the name of your table,
<YOURNEWFIELD> is the name of your new field,
<YOURNUMBERASTEXTFIELD> is the name of the field where the numbers are stored as text.>

 

[neileg]

As far as I know, CDbl() can't cope with leading spaces where as Val() can. Depends, then, on the format of your data.

However, running an update query is not neccesary, a select query with the calculated field will do the trick.

 

[boblarson]

Trim will also work, so you could do Cdbl(Trim([YourField]))

 

[Leen]

still don't understand the change data-type

Hi,
I'm having the same problem: I want to convert the data type of a field in a table using a macro/query/.. but I don't understand your solution explained on this forum:

I want to change the datatype of the field named "Manid5" in the table "Sheet1" from text into number (double).

So I made an updatequery:
Field: Manid5
Table: Sheet1
Update To: CDbl("[Manid5]")

But it doesn't want to work en gives a fault. Can somebody explain me?

Thanks a lot!
Leen

 

[boblarson]

You can't update the data within the table to a different datatype like that. My code was for conversion to use in a query, or such, but not to change the underlying data.

To change the actual datatype within the table, will require you to either create a SQL Statement using the ALTER TABLE method, or to go in and open the table in design mode and physically change the datatype which may, or may not, be possible due to potential datatype violations. If converting from text to numbers you are more likely to suffer violations if the text includes things other than numbers. Going the other direction, numbers to text, you wouldn't get any violations.

 

[Leen]

Thanks a lot for your help! The Alter Table method works (I wanted to automise the process, so changing manually is not an option).