?
Solved

Seperate numbers

Posted on 2005-04-12
22
Medium Priority
?
294 Views
Last Modified: 2011-09-20

got a db where a field is called "Money"

so when i want to display the money on the webpage i do this :

Cash money : <%=rs("money")%>

what's the problem now :
the money is 1 000 000

The problem is that it displays this : 1000000
i want it to be 1 000 000

how can i do that

regards
0
Comment
Question by:Gertje
  • 9
  • 5
  • 5
  • +2
22 Comments
 
LVL 9

Expert Comment

by:sudheeshthegreat
ID: 13765748
what's the DBMS and datatype of the field named 'Money'?
0
 
LVL 9

Expert Comment

by:danataylor
ID: 13765838
Use this in your select statement:

select to_char(money, '999G999G999', 'NLS_NUMERIC_CHARACTERS=''* ''') as money from ...
0
 
LVL 9

Expert Comment

by:danataylor
ID: 13765844
NOTE: All the quotes above are single-guotes
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Gertje
ID: 13766122
sql = "SELECT username,money,profit,country FROM users where totport='ok'"&"order by totportvalue desc"  
With objCn
      .CursorLocation = adUseClient
      .ConnectionTimeout = 15
      .CommandTimeout = 30
      .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                    "Data Source=" & Server.MapPath("dblorewillems/users.mdb") & ";"
      .Open

End With

if i use your code it gives this error
Undefined function 'to_char' in expression.

--------------------------------------------------
what's the DBMS and datatype of the field named 'Money'?

it is numeric , lang integer , standard notion.

regards
0
 
LVL 6

Expert Comment

by:masirof
ID: 13766163
You may want to use:

FormatCurrency(rs("money"))
0
 

Author Comment

by:Gertje
ID: 13766203
that give a $ sign and  , between the numbers

i only want the spacings 1 000 000 thats what i want

regards
0
 
LVL 9

Expert Comment

by:danataylor
ID: 13766316
The solution I gave might only work for Oracle databases.  Your SQL would be:

sql = "SELECT username,to_char(money, '999G999G999', 'NLS_NUMERIC_CHARACTERS=''* ''') as money,profit,country FROM users where totport='ok'"&"order by totportvalue desc"  

Just cut-n-paste and see if it works.
0
 

Author Comment

by:Gertje
ID: 13766449
Microsoft JET Database Engine error '80040e14'

Undefined function 'to_char' in expression.


still that error
0
 
LVL 6

Expert Comment

by:masirof
ID: 13766495
You can use Left Right Mid functions.

For example (This is very specific though, only for your example, but can be implement for more compatibility):

mon = "1000000"
mon = FormatNumber(mon,0)
response.Write(Replace(mon,".","&nbsp;"))
0
 
LVL 9

Expert Comment

by:danataylor
ID: 13766576
M$ Jet probably doesn't do "T0_Char".  They use another syntax.  At any rate they probably don't support the NLS_NUMERIC_CHARACTERS type either.
You'll probably have to do it in code.

I think masirof is on the right track.

M = replace(replace(FormatCurrency(rs("money")), ","," "), "$","")
0
 
LVL 14

Expert Comment

by:kiddanger
ID: 13766677
function thous(str)
dim t, r
do while len(str) > 3
  t = " " & right(str,3)
  str = left(str,len(str)-3)
  r = t & r
loop
thous = str & r
end function

dim a
a = thous("1000000")
Response.Write a
0
 
LVL 9

Expert Comment

by:danataylor
ID: 13766773
Found the correct syntax to do it in the select statement:

 Format([money],"""999 999 999""") AS money
0
 
LVL 14

Expert Comment

by:kiddanger
ID: 13766974
a little shorter...

function thous(str)
dim t
do while len(str) > 3
  t = t & " " & right(str,3)
  str = left(str,len(str)-3)
loop
thous = str & t
end function

dim a
a = thous("1000000")
Response.Write a
0
 
LVL 14

Expert Comment

by:kiddanger
ID: 13767030
dana...

Does that work if the number is: 1000000000000000000000000 ?
0
 

Author Comment

by:Gertje
ID: 13767564
Expected end of statement

/rankingtest.asp, line 56

sql = "SELECT username,Format([money],"999 999 999") as money,profit,country FROM users where totport='ok'"&"order by totportvalue desc"
----------------------------------------------^
0
 
LVL 9

Accepted Solution

by:
danataylor earned 680 total points
ID: 13768134
Boy, I had a bunch of problems with that query.  Cut and pasted the wrong line.  Ran this through M$ Access and it worked for me:

SELECT Format([money],"### ### ### ###") AS ["money"] FROM Table1;

Works up to 999,999,999,999  Not sure if there is a generic way to code this so it works for any number of any size.
0
 
LVL 9

Expert Comment

by:danataylor
ID: 13768156
For your SQL statement you need to double-up internal quotes in a string - Like:

sql = "SELECT username,Format([money],""### ### ### ###"") as ""money"",profit,country FROM users where totport='ok'"&"order by totportvalue desc"
0
 
LVL 9

Expert Comment

by:danataylor
ID: 13768190
kiddanger

Your solution is much more generic since it will work for any size number - as long as it can be converted to a string.  My personal preference is to take care of everything I can in the SQL query.  The resulting data can then be just stuffed into tables and/or reports without a lot of post-processing.  As I said, that's just my preference - probably a throwback to using views and stored-procedures when possible.
0
 

Author Comment

by:Gertje
ID: 13768949
thank you
0
 
LVL 14

Expert Comment

by:kiddanger
ID: 13774520
dana...

Show me something that cannot be converted to a string.  My preference would be to do it in the database.

0
 
LVL 9

Expert Comment

by:danataylor
ID: 13774590
Would that fall under the category of String Theory?  We could open a thread in "Math & Science".
If I remember correctly, the answer to life, the universe, and everything is "42".  ;)
0
 
LVL 14

Expert Comment

by:kiddanger
ID: 13774952
I thought so.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question