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,118
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,344
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)])..........



??

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 
bobdee
post Mar 16 2017, 11:26 AM
Post#3



Posts: 1,118
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: 219
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.


--------------------
| Mark Klein | Access 2010 | Windows 10 | Visual Studio 2013
Go to the top of the page
 
JonSmith
post Mar 16 2017, 12:42 PM
Post#5



Posts: 3,086
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,344
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.

--------------------
"Nuclear" Nick
____________
The top three reasons to hide code; 1) It's not your own. 2) It's your own, but it's so crappy you don't want anyone to see it. 3) The comments in your code would get you in a lot of trouble if ever made public.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th August 2017 - 08:28 AM