Access Coversion Text to Number

[DaleBlackwell]

I have a rather large amount of data in Access. I have a number of similar fields in a table which are data type number. In error one of the fields has been set to text. In each row only one of these fields is populated ie the other fields are empty, presumably null ?

The data has been loaded from daily data files over large period of time.

I have tried changing the field properties to number but system runs out of memory.

I have set up a query and built an expression with CLng in another column which converts the data to number when there is text data but I can only get #Error when the text data field is empty. I have tried nesting in IIF etc.

How can I achieve getting the calculated field to show nothing or zero would suffice ? At the end of the day I am trying to populate a pivot table in Excel with these fields using SUM (the creation of the pivot takes many hours).

 

[SkipVought]

Hi,

What kind of field would have text that can be converted to numeric?

ALSO...

"In each row only one of these fields is populated ie the other fields are empty, presumably null ?"

This raises a [red]RED FLAG[/red]. You might consider a different table design that would be normalized, something like...
[tt]
......| Category | Amount
[/tt]


Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[DaleBlackwell]

The field has numeric data but mistakenly it is set as text.

 

[dhookom]

I think I see the same Red Flag as Skip.
You might be able to get by with creating a query that uses

SELECT ..., Val(Nz([TextField],0)) as TheValue, ...

If that doesn't work then you might want to figure out the real value in the field.

Duane
Hook'D on Access
MS Access MVP