Print 0 decimal places if number is whole, otherwise printing to 2 decimal places
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?
-
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
1 -
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 -
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 -
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 -
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 -
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 -
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 -
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 -
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 -
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 -
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 -
No worries.
My VB was configured to work as a field specific script rather than a Document Level routine, which I hardly ever use.
0
Bitte melden Sie sich an, um einen Kommentar zu hinterlassen.
Kommentare
12 Kommentare