Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Export numeric data to excel as numbers 2

Status
Not open for further replies.

GThrower

Programmer
Apr 16, 2001
9
US
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.
 
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
 
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
 
You can change the output of the IIF statement to other than strings.. Try something like this:

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


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

HTH Joe Miller
joe.miller@flotech.net
 
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.
 
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
 
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")
Joe Miller
joe.miller@flotech.net
 
That is what I had tried but it cause the whole column to become text. I'll have to figure out something else.

gary
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top