convert text to number 1

[sciclunam]

Hi all,

How can I convert a text record, dbrec("curvalue"), which contains for example 4.32 to a number with 4 decimal places, like 4.3200


I tried

formatnumber(cdbl(dbrec("curvalue")),4)

but I'm getting type mismatch cdbl.

Thanks

Mario

Getting married in Malta?
Visit

http://www.TheWeddingSite.com

 

[Tarwn]

Maybe something like:

If isNumeric(dbrec("curvalue")) Then
   myNumber = FormatNumber(cDbl(dbrec("curvalue")))
Else
   myNumber = 0
End If

-T

[sub]01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111[/sub]
Need an expensive ASP developer in the North Carolina area? Feel free to let me know.

 

[SkipVought]

Mario,

STORING a numeric value has NOTHING to do with FORMAT! 4.23 is 4.23

DISPLAYING a numeric value has EVERYTHING to do with FORMAT. 4.34 could be DISPLAYED...
[tt]
4
4.3
4.34
3.340
....
[tt]
Does NOT change the VALUE!

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[sciclunam]

Hi,
Thaks for your replies.

Infact this is what I want...change how it is displayed.

4.23 is being extracted from a column in a table which is set as 'text' and we cannot change it to number due to some other things.

I want to change how it is displayed. So how can the text 4.23 be displayed on a web page 4.2300 ie: to 4 decimal place.

I assume that

1. Have to change it to number (on the page not on db)
2. Then format it to 4 decimal places.

So, correct me if I'm wrong, the code where this number should be displayed must be as follows...

FormatNumber(cDbl(dbrec("curvalue")))

Don't worry about having text characters in "curvalue" as I have already filtered that.

Thanks guys.

Getting married in Malta?
Visit

http://www.TheWeddingSite.com

 

[sciclunam]

Tried FormatNumber(cDbl(dbrec("curvalue")))

Still getting the error..."type mismatch cdbl"


Getting married in Malta?
Visit

http://www.TheWeddingSite.com

 

[DreXor]

ensure the db value isn't zero length or null.

DreX
aKa - Robert

 

[sciclunam]

No I have records.

The full line of code is

<%=iif(dbrec("curname")<>"EURO",iif(dbrec("curvalue")="0","N/A",dbrec("curvalue")),dbrec("curvalue"))%></div></td>

Tha above work but I need to add the trailing zeors if curvalue is less than 4 decimal places...so I tried...

<%=iif(dbrec("curname")<>"EURO",iif(dbrec("curvalue")="0","N/A",formatnumber(cdbl(dbrec("curvalue")))),dbrec("curvalue"))%></div></td>

which gives me type mismatch cdbl!

Getting married in Malta?
Visit

http://www.TheWeddingSite.com

 

[Tarwn]

Is it possible you have any fields that contain text, blank entries, or anything else non-numeric? (thats why I suggested the If isNumeric above)

Also make sure they don't have any currency signs, as that will mess up cDbl also.

-T

[sub]01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111[/sub]
Need an expensive ASP developer in the North Carolina area? Feel free to let me know.

 

[sciclunam]

Hi Tarwn,

No they are all numeric except the EURO but that I already took care of with the iif statement...

<%=iif(dbrec("curname")<>"EURO",iif(dbrec("curvalue")="0","N/A",formatnumber(cdbl(dbrec("curvalue")))),dbrec("curvalue"))%></div></td>

Also no currency symbols are involved. The text N/A above is not supposed to be causing this. The db is Microsoft Access.

You can check the page to see on

http://www.centralbankmalta.com/site/exchangerates/getspotrates.asp

Getting married in Malta?
Visit

http://www.TheWeddingSite.com

 

[ignoreme]

Have you tried removing some of the syntax to see if its an arrangement problem. At first glance, it does not look right with syntax arrangement. Considering the IIf function should be:

variant = IIf(expression, truecondition, falsecondition)

Whereas I see:

iif(dbrec("curname")<>"EURO",iif(dbrec("curvalue")="0","N/A", formatnumber(cdbl(dbrec("curvalue")))),dbrec("curvalue"))

expression: dbrec("curname")<>"EURO"
truecondition?:iif(dbrec("curvalue")="0","N/A"
falsecondition: formatnumber(cdbl(dbrec("curvalue")))
truecondition: dbrec("curvalue")

Or maybe I'm not seeing it correctly?

 

[Tarwn]

Right now he has embedded statements, so it would break down to something like:
expression_0: dbrec("curname")<>"EURO"
true_0:
expression_1: dbrec("curvalue")="0"
true_1: "N/A"
false_1: formatnumber(cdbl(dbrec("curvalue")))
false_0: dbrec("curvalue")

There is no built-in protection here for empty strings, NULL values, strings with text in them, etc.

I would suggest writing it out into longer format for maintainability.

A better solution would be:

If ucase(dbrec("curname)) = "EURO" Then
   Response.Write dbrec("curvalue")
ElseIf dbrec("curvalue") = 0 Then
   Response.Write "N/A"
ElseIf Not isNumeric(dbrec("curvalue")) Then
   Response.Write dbrec("curvalue")
Else
   Response.Write FormatNumber(dbrec("curvalue"),4)
End If

Now you are protected against EURO, 0, non-numbers.

If they were truly all numbers except for EURO or 0's (which are numbers) then you wouldn't be getting cDbl errors. My guess is that if they all look like numbers you need to apply a trim statement before the cDbl.

-T
[/code]

[sub]01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111[/sub]
Need an expensive ASP developer in the North Carolina area? Feel free to let me know.

 

[sciclunam]

Hi Thanks for your help.

Yes, mine is a nested iif statement. Tarwn, I shall try your suggestion that later on today.

Tarwn you wrote "If they were truly all numbers except for EURO or 0's (which are numbers)"

PS: EURO is NOT numeric. All other fields are.

What I wanted to make clear is that my statement works...I just want to display 4 decimal palces in the numeric values.

In words my statement says:

1. If the currency IS Euro - just display the value (this will contain numeric and non numeric) No problem with this it works.

2. If it is NOT EURO....

2b....and value is "0" - display N/A. There can never be a "0" value in Euro. - This works

2c....and value is NOT "0" then display the value - and this is what I wish to format to 4 decimal places. It works but obviously without the formatting as it gives the error above.

I confirm. The table contains only numeric values except in the EURO. Don't know if you have already gave it a look but this is the page showing all the records of the table...

http://www.centralbankmalta.com/site/exchangerates/getspotrates.asp

The N/A records have been created at run-time. They are all numeric!



Getting married in Malta?
Visit

http://www.TheWeddingSite.com

 

[tsuji]

Hello sciclunam,

I would suggest you response.write the escaped dbrec("curvalue"); you would probably see some formatting code (appearing as %hh etc) which would provoke a type mismatch, (space, vbtab, vbcrlf will not).

[tt][green] response.write escape(dbrec("curvalue"))[/green][/tt]

regards - tsuji

 

[sciclunam]

Hi,

The code by Tarwn worked perfect. Thanks

CODE
If ucase(dbrec("curname")) = "EURO" Then
Response.Write dbrec("curvalue")
ElseIf dbrec("curvalue") = 0 Then
Response.Write "N/A"
ElseIf Not isNumeric(dbrec("curvalue")) Then
Response.Write dbrec("curvalue")
Else
Response.Write FormatNumber(dbrec("curvalue"),4)
End If

Getting married in Malta?
Visit

http://www.TheWeddingSite.com