Export numeric data to excel as numbers 2

[GThrower]

I have been trying to force access 2000 to export a query as an excel 2000 and have the numbers show up as numbers. Currently the numbers show up in excel as text. Anyone have any suggestions? I did a keyword search and didn't find anything.

Gary T.

 

[JoeMiller]

Copy/Paste may work... Are you sure the field in question is set as Number in Access? Because Access and Excel negotiate data types properly when exporting and don't cross lines.. If the data field is specified as string (even though it contains number) Excel will bring it in as a string.

Joe Miller
joe.miller@flotech.net

 

[GThrower]

I've been looking at this closer and what we were trying to do is use IIF to change some data. It appears that IIF outputs everything as strings. I am now working on a different approach.

Gary

 

[JoeMiller]

You can change the output of the IIF statement to other than strings.. Try something like this:

IIF(x=y,Value(MyField),"&quot


Value() converts anything in the parenthesis, in this example MyField, to numeric data.

HTH Joe Miller
joe.miller@flotech.net

 

[GThrower]

Thanks that worked. Any idea of a way to make it work and be able to have it output "NA" if the field is empty without causing the entire column to revert to text?

Gary

PS: The function is val(), not value(), in access 2000 even though value() shows up in help you get an error if you try to use it.

 

[MichaelRed]

there are other problems in the use of Val(Expr), as ANY non-numeric character will STOP the trranslation. I suggest that you look in to the Coercion functions (CCur, CSgl, CInt ...)

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[JoeMiller]

I believe the whole column will convert to text if you use NA, but you can try it with this:

IIF(Len(MyField)>0,Value(MyField),"NA&quot
Joe Miller
joe.miller@flotech.net

 

[GThrower]

That is what I had tried but it cause the whole column to become text. I'll have to figure out something else.

gary