Seperate numbers


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
GertjeAsked:
Who is Participating?
 
danataylorCommented:
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
 
sudheeshthegreatCommented:
what's the DBMS and datatype of the field named 'Money'?
0
 
danataylorCommented:
Use this in your select statement:

select to_char(money, '999G999G999', 'NLS_NUMERIC_CHARACTERS=''* ''') as money from ...
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
danataylorCommented:
NOTE: All the quotes above are single-guotes
0
 
GertjeAuthor Commented:
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
 
masirofCommented:
You may want to use:

FormatCurrency(rs("money"))
0
 
GertjeAuthor Commented:
that give a $ sign and  , between the numbers

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

regards
0
 
danataylorCommented:
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
 
GertjeAuthor Commented:
Microsoft JET Database Engine error '80040e14'

Undefined function 'to_char' in expression.


still that error
0
 
masirofCommented:
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
 
danataylorCommented:
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
 
kiddangerCommented:
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
 
danataylorCommented:
Found the correct syntax to do it in the select statement:

 Format([money],"""999 999 999""") AS money
0
 
kiddangerCommented:
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
 
kiddangerCommented:
dana...

Does that work if the number is: 1000000000000000000000000 ?
0
 
GertjeAuthor Commented:
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
 
danataylorCommented:
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
 
danataylorCommented:
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
 
GertjeAuthor Commented:
thank you
0
 
kiddangerCommented:
dana...

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

0
 
danataylorCommented:
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
 
kiddangerCommented:
I thought so.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.