formatnumber function returns error

E

eddie wang

The following code with formatnumber function returns me the following
code. Why? Thanks.
<td align="right"><Font
class=content4><%=formatNumber(ars.Fields("SOLD_AMOUNT"),2)%></td>

Microsoft VBScript runtime error '800a000d'

Type mismatch: 'formatNumber'
 
E

Evertjan.

eddie wang wrote on 11 feb 2004 in microsoft.public.inetserver.asp.general:
<%=formatNumber(ars.Fields("SOLD_AMOUNT"),2)%>

Microsoft VBScript runtime error '800a000d'

Type mismatch: 'formatNumber'

Make sure ars.Fields("SOLD_AMOUNT") is always a number
 
E

eddie wang

There is null value. IS there anyway to use formatnumber and other
functions to say if there is null then not to use formatnumber, else use
formatnumber??

Thanks.
 
B

Bob Barrows [MVP]

eddie said:
There is null value. IS there anyway to use formatnumber and other
functions to say if there is null then not to use formatnumber, else
use formatnumber??

Thanks.

Create a NullToZero function:

function NullToZero(pData)
if len(pData) = 0 then
NullToZero = cdbl(0)
else
on error resume next
pData = cdbl(pData)
if not IsNumeric(pData) then pData = 0
NullToZero = cdbl(pData)
end if
end function

Then use the function in your call to FormatNumber:
<%=formatNumber(NullToZero(ars.Fields("SOLD_AMOUNT")),2)%>

HTH.
Bob Barrows
 
A

Aaron Bertrand - MVP

Why is there a NULL value? Why are you displaying a NULL value? Why are
you pulling a NULL value from your database? Can you not make the query
return 0 if it's NULL, or does that have a different meaning?
 
E

eddie wang

0 means there is a value of 0. Null means there is no value. There is
difference between 0 and null.
 
B

Bob Barrows [MVP]

eddie said:
0 means there is a value of 0. Null means there is no value. There
is difference between 0 and null.
You cannot use FormatNumber on an empty string or a Null value. If you do
not want to display anything when the field contains a Null, then you will
need to use some logic to determine whether the value is written to the
response or not:
<%=
if len(ars.Fields("SOLD_AMOUNT")) > 0 then
formatNumber(ars.Fields("SOLD_AMOUNT"),2)
end if
%>

Bob Barrows
 
A

Aaron Bertrand - MVP

0 means there is a value of 0. Null means there is no value. There is
difference between 0 and null.

Thanks, I understand the difference between 0 and NULL. I was asking if it
matters for YOUR scenario (often it doesn't, and NULLs weren't meant to be
allowed in the database).

If you need to differentiate between 0 and NULL, then you will need to check
somehow, either by including a flag in the query or checking the actual
value as Bob showed.
 
E

eddie wang

This NullToZero function will replace all the null values to 0. Is
there a way to display all the null values as they are and to display
the rest with two decimals? Thanks.
 
A

Aaron Bertrand - MVP

This NullToZero function will replace all the null values to 0. Is
there a way to display all the null values as they are and to display
the rest with two decimals? Thanks.

What does "as they are" mean? Do you want the word NULL to be written to
the screen, or do you want an empty space, or do you want "n/a", or do you
want "I don't know why I have NULLs here", or do you want something else?
 
B

Bob Barrows [MVP]

eddie said:
This NullToZero function will replace all the null values to 0. Is
there a way to display all the null values as they are and to display
the rest with two decimals? Thanks.

I think I answered this in my other post.

Bob Barrows
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,482
Members
44,901
Latest member
Noble71S45

Latest Threads

Top