Digging another one off my pile of backlogged blog post ideas because someone else asked me about it this week. If you struggle with numeric formats in your WEBDEV projects, you are not alone. So let’s take a look at a few things and see what we use as out best practice.
First, let’s look at the settings as they come “out of the box”. I am not sure when this project-level setting came about, it may be a side effect of the intergraded IDE, but you will notice by default it wants to set the formatting of numbers based on the linguistic options of the operating system.
That would seem to make a lot of sense, but it has always lead to unexpected results for me in a WEBDEV project. Let’s look at the other side of the coin, our edit controls, again “out of the box” it wants us to set them to “Numeric defined by the project”
Again seems like a great idea, until we put it into action. I typed 15000 into the edit control and pressed tab, this is what I got when testing via GO
That’s right it forced 2 decimal places and used a , for the decimal point. BTW, for the uninitiated pretty much the entire world uses , for the decimal point other than the US and UK. My theory is that the browser doesn’t get all of the correct linguistics options from the OS and WEBDEV is defaulting back to it core logic which would be french formatting with a , for a decimal point and a space for the thousand separator. I also suspect this could get yet a different response when published to a web server, depending on the server os, hosting software (IIS/Apache), phase of the moon, etc. But let’s forget all of that for a bit, notice it is forcing 2 decimal places with I do “as defined by the project”. Well, that ain’t great if I wanted whole numbers, etc. So I find I can’t really use that setting anyway, and always end up setting the formatting myself. So let’s do that, say we want to allow up to 999,999.99 in this field and support positive and negative numbers. That is even one of the list box choices from the format popup.
But if you select that, and test again via GO, you will discover 15000 still gets formatted like above. So let’s go back over to the project settings and force the formatting of numbers instead of using the linguistics of the OS.
And finally, our 15000 looks correct!
BTW, the exact same type of settings exists for Currency as well, so you should change them there as well.
Bonus Material edit control arithmetic
Now that we have our numbers displaying correctly. Let’s look at doing some arithmetic with them. If you have been using WEBDEV for very long you likely have discovered that edit controls can sometimes be treated always strings, and this can lead to some unexpected results.
Let’s look at these 2 statements
"Version 1:" + edtNumber1 + edtNumber2 "Version 2:" + (edtNumber1 + edtNumber2)
Those parentheses make a big difference in the results below
Version 1:100-10 Version 2:90
The best practice is to always do math in a numeric field first and always wrap edit controls with the Val function. It’s a bit of belt and suspenders that you don’t always need, but let me tell you from experience a bug caused by an edit control being treated as a string is hard to find and can introduce so very bad data into your database.
tmpVar is numeric = Val(edtNumber1) + Val(edtNumber2) stcServerResults += [CR] + "Version 3:" + tmpVar
The definition of your edit control also plays a role, so far we have been using two edit controls defined as numeric using the formatting we set up in the first part of this article. If we change the format of edtNumber1 to be text, let’s see what happens
Server Results Version 1:100-10 Version 2:100-10 Version 3:90
Notice version 2 no longer works but 3 does. That is why I said the best practice was to use a temporary numeric and wrap the controls with Val. At the very least always wrap your controls with Val, as you see Version 4 will also work in this scenario, but it sure is getting complicated with the parentheses and it only takes one misplaced parenthesis to completely change the result.
"Version 4:" + (Val(edtNumber1) + Val(edtNumber2)) Version 4:90
BTW, it would also be a good idea to read the online help concerning the Val function. It does some very interesting things
- It evaluates the string character by character and stops as soon as it hits a string that it doesn’t think is part of a number, so
- Val(“90+10”) = 90
- Val(“90ABC”) = 90
- Val(“90D1”) = 900
- Wait you say! See Rule #2
- It evaluates standard scientific notation, which is why 90D1 above = 900
So now let’s go for the gold, and really complicate life! Accounts want parentheses to indicate negative numbers, not the negative sign, let’s see what kind of wrench that will throw into the works.
First, we need to go back over to the project settings and change the Negative format
Our first issue is this only helps us if the edit control format is set to “defined by the project”. And we already discussed above the downside to that. But that is ok when we talk about accountants we are generally only talking about currency, so let’s revisit our project settings but this time, use the currency tab
And we change the control type to a Currency. Notice there are two “defined by the project” options, which determine if the currency symbol is displayed or not.
I changed both “defined by the project” so that the currency symbol isn’t displayed and this is the way the edit controls look.
So far so good! But you may have noticed in my code I had some that results said Server Results. That is because I have been riding in this Rodeo long enough to know that the browser and server don’t always act the same!
Our Server results look good
Server Results Version 1:100-10 Version 2:90 Version 3:90 Version 4:90
But the exact same code in the browser does not!
Browser Results Version 1:10010 Version 2:110 Version 3:110 Version 4:110
And just for review here is the code
stcBrowserResults = "Browser Results" stcBrowserResults += [CR] + "Version 1:" + edtNumber1 + edtNumber2 stcBrowserResults += [CR] + "Version 2:" + (edtNumber1 + edtNumber2) tmpVar is numeric = Val(edtNumber1) + Val(edtNumber2) stcBrowserResults += [CR] + "Version 3:" + tmpVar stcBrowserResults += [CR] + "Version 4:" + (Val(edtNumber1) + Val(edtNumber2))