Convert value from -00005.55 to -5.55
Moderators: chulett, rschirm, roy
Convert value from -00005.55 to -5.55
Hi, would like to convert -00005.55 to -5.55. Field is a decimal field length of 5 and a scale of 2.
Thanks
Thanks
Code: Select all
If Format.PAID < 0 Then '-':Trim(Convert(' ','',(-1*Format.PAID)),"0","L") Else Trim(Convert(' ','',(Format.PAID)),"0","L")
You are the creator of your destiny - Swami Vivekananda
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 246
- Joined: Mon Jun 30, 2008 3:22 am
- Location: New York
- Contact:
If we split the logic accross 3 stage variables it works. Our output is a varchar field. But not sure why we needed to do it this way; please read below.
example (the logic below works when splitting the formula)
svPaidIn
If Format.PAID < 0 Then Format.PAID * -1 Else Format.PAID
svPaid
Trim(Convert(' ','',(svPaidIn)),"0","L")
svPaidTarget
If Format.PAID < 0 Then '-':svPaid Else If svPaid[1,1] = "." Then "0":svPaid Else svPaid
but when we do the logic below it puts 0's to the right. Same formula as above, but not split accross multiple lines/stage variables like we saw in the previous example. Is it doing the Trim(Convert first them multiply by -1? Not sure why it doesn't do the correct order of operations when all together in one formula, but when we split it out it works?
svPaid
If Format.PAID < 0 Then Trim(Convert(' ','',(Format.PAID *-1)),"0","L") Else Trim(Convert(' ','',(Format.PAID)),"0","L")
svPaidTarget
If Format.PAID < 0 Then '-':svPaid Else If svPaid[1,1] = "." Then "0":svPaid Else svPaid
example (the logic below works when splitting the formula)
svPaidIn
If Format.PAID < 0 Then Format.PAID * -1 Else Format.PAID
svPaid
Trim(Convert(' ','',(svPaidIn)),"0","L")
svPaidTarget
If Format.PAID < 0 Then '-':svPaid Else If svPaid[1,1] = "." Then "0":svPaid Else svPaid
but when we do the logic below it puts 0's to the right. Same formula as above, but not split accross multiple lines/stage variables like we saw in the previous example. Is it doing the Trim(Convert first them multiply by -1? Not sure why it doesn't do the correct order of operations when all together in one formula, but when we split it out it works?
svPaid
If Format.PAID < 0 Then Trim(Convert(' ','',(Format.PAID *-1)),"0","L") Else Trim(Convert(' ','',(Format.PAID)),"0","L")
svPaidTarget
If Format.PAID < 0 Then '-':svPaid Else If svPaid[1,1] = "." Then "0":svPaid Else svPaid
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: