UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Formatting A Number But Keeping As A Number, Access 2010    
 
   
bobdee
post Mar 16 2017, 11:07 AM
Post#1



Posts: 1,136
Joined: 15-January 06



When I use the Format function in an SQL statement, I get a string. But when I try to export the query to Excel, the "number" is invariably shows up as Text in Excel. Is there any way to format a number, yet still have it recognized as a number instead of a string or text?

Example:

SELECT Format(ELEC_USAGE*3.412+FF_USAGE,'#,##0.0') As [Energy Use (MBTU/yr)]..........
Go to the top of the page
 
nuclear_nick
post Mar 16 2017, 11:22 AM
Post#2



Posts: 1,662
Joined: 5-February 06
From: Ohio, USA


Have you tried one of the conversion functions?

CODE
SELECT Cdbl(Format(ELEC_USAGE*3.412+FF_USAGE,'#,##0.0') As [Energy Use (MBTU/yr)])..........


or...

CODE
SELECT Clng(Format(ELEC_USAGE*3.412+FF_USAGE,'#,##0.0') As [Energy Use (MBTU/yr)])..........



??
Go to the top of the page
 
bobdee
post Mar 16 2017, 11:26 AM
Post#3



Posts: 1,136
Joined: 15-January 06



Yeah, I tried CDbl exactly as you show it. It removed the formatting.
Go to the top of the page
 
mklein
post Mar 16 2017, 12:35 PM
Post#4



Posts: 264
Joined: 7-August 12
From: BC, Canada


Formatting is most effectively applied as the last step in creating a presentation. If, for instance, you produce data for output to Excel, it would be most effective to apply final formats in Excel. Otherwise you run into this exact hazard, that the number, once formatted, becomes text, and is no longer useful for downstream calculability.
Go to the top of the page
 
JonSmith
post Mar 16 2017, 12:42 PM
Post#5



Posts: 3,987
Joined: 19-October 10



I was going to say the same as Mark.
Dont format it in SQL then. Do it in Excel.
Go to the top of the page
 
nuclear_nick
post Mar 16 2017, 01:59 PM
Post#6



Posts: 1,662
Joined: 5-February 06
From: Ohio, USA


I believe it also depends on how you export to Excel.

I've not any issues for the past few years using 'copy from recordset'. There are quite a few methods of exporting to Excel.
Go to the top of the page
 
slacka
post Dec 10 2017, 02:44 AM
Post#7



Posts: 13
Joined: 2-December 17



This is what i do. Set the Excel field type to the variable type you want. Then copy your value there.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    14th December 2018 - 12:16 AM