Print 0 decimal places if number is whole, otherwise printing to 2 decimal places S’abonner

0
Avatar
Lee Barber

Not sure if I'm posting this in the correct area.

I'm printing prices on our labels. I want them to print whole numbers to 0 decimal places, but anything else to the normal 2 decimal places.

eg:
$10 for 10.00
$10.50 for 10.50
etc.

Currently I have to manually change the decimal places in the Data Type tab. Is there a way to automate this so Bartender will automatically print whole numbers without the decimals, but have non-whole numbers print to 2 decimal places?

12 commentaires

1
Avatar
Pete Thane
Actions pour les commentaires Permalien

I dont think there is a simple way to do this but you could use a VBScript routine to achieve this.

The following code looks at the value of a a field named MoneyInput looks to see if the last two digits are greater than zero and if so prints the full number but if not strips of the last three digits and prints just the parts before the decimal point.

 

Value = Format.NamedSubStrings("MoneyInput").Value

Value1 = Right(Value,2)

Value2 = Len(Value)

Value2 = Value2 - 3

If Value1 > 0 then

Value = Value

Else Value = Left(Value,Value2)

end if

Value = Value

For the dollar sign you could ether add a Prefix on the Transforms tab or else amend the last line to 

Value = "$" & Value

 

I am guessing your prices are coming from a database and so to implement this you should configure this field to be a Multi Line Script triggering on an OnNewRecord event. The first line will also need changing, just type in the Value = part and then in the column on the right double click on your database field with the price in it, My label is not linked to a database but this screenshot should give you an idea of what I mean.

I hope this helps

Pete

 

0
Avatar
Lee Barber
Actions pour les commentaires Permalien

Thanks Pete. You'll have to excuse my ignorance, I'm new to VB in Bartender. I have a little experience with it in Excel, but not enough to really understand what I'm doing.

I've followed along as best I could, but I think I've made a mistake somewhere as the labels are still printing incorrectly. I've attached a screenshot. Is there something I'm missing or doing wrong?

The fields come from an Excel sheet acting as a database, with "POSPrice" being the field I need to modify.

0
Avatar
Pete Thane
Actions pour les commentaires Permalien

Not sure that all looks correct. Can you send me a screenshot of your label that includes a field on it (without any VB or transforms) that shows how the data in the database is appearing on the label. 

0
Avatar
Pete Thane
Actions pour les commentaires Permalien

Just quickly mocked a datafile and label and the above code works as long as the Data Type of the VB script field is set to Text and not Currency 

0
Avatar
Lee Barber
Actions pour les commentaires Permalien

Thanks Pete, that was the major issue, I had the Data Type as Currency Still. The script is working now. The only issue is that it's cutting off the 2nd decimal if it's a 0. eg: $5.50 shows up as $5.5 as in the screenshot. $5.55 will show up correctly though.

0
Avatar
Pete Thane
Actions pour les commentaires Permalien

I guess the the value is 5.5 in the database and not 5.50. 

Keep the first line of code and replace the rest with this

Value1 = Value * 100

Value2 = Len(Value1)

Value3 = Right(Value1,2)

If Value3 = 0 then

Value3 = ""

else Value3 = "." & Value3

End if

Value4 = Value2-2

Value4= Left(Value1,Value4)

Value = "$" & Value4 & Value3

Value = Value

 

Hopefully this should work. There are no doubt much better ways of doing this but I only know bits and pieces of VB and so the routines are probably longer than they need to be, but as long as they work..... 

0
Avatar
Lee Barber
Actions pour les commentaires Permalien

I tried that out and it didn't change anything. If the 2nd decimal is 0, it still gets cut off. I'm not sure what Value4 is referring to in the script?

The value in the excel sheet is 5.50, but I think Bartender ignores cell formatting and just ignores leading or trailing 0's. 

0
Avatar
Pete Thane
Actions pour les commentaires Permalien

Not sure what to suggest as this works in my sample linked to an Excel spreadsheet . The ODBC driver is stripping off the extra zeroes I think as the bottom two records in the spreadsheet have the prices set at 11.50 and 13.00 respectively

 

 

0
Avatar
David Morley
Actions pour les commentaires Permalien

This has worked for me, thank you!

How would i go about added in a comma as well?

Eg $1,000 instead of $1000, while maintaining the decimal function.

0
Avatar
Pete Thane
Actions pour les commentaires Permalien

This should do the trick although you will need to amend your first line to link it to your database field with the value in it

Value = Format.NamedSubStrings("startno").Value

Value1 = Value * 100

Value2 = Len(Value1)

Value3 = Right(Value1,2)

If Value3 = 0 then

Value3 = ""

else Value3 = "." & Value3

End if

Value4 = Value2-2

Value4= Left(Value1,Value4)

Value = Value4 & Value3

Value = FormatCurrency(Value)

Value1 = Len(Value)

Value2 = Right(Value,2)

Value3 = Value1-3

If Value2 > 0 then

Value = Value

else Value = Left(Value,Value3)

end if

Value = Value

0
Avatar
Lee Barber
Actions pour les commentaires Permalien

Hi Pete,

I meant to post this a couple months ago. I got it working in the end. It was cutting off the 0 in the second decimal place no matter what I did. So I tried moving it to "Procedures for Transform Events" for the specific field I was trying to modify and now it's working perfectly.

0
Avatar
Pete Thane
Actions pour les commentaires Permalien

No worries. 

My VB was configured to work as a field specific script rather than a Document Level routine, which I hardly ever use.

Vous devez vous connecter pour laisser un commentaire.