472,110 Members | 2,196 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,110 software developers and data experts.

Storing and reading decimal numbers


I've got some code in VBA where I calculate some numbers. Now some of them are large decimal numbers like -6,3216324E03. So in scientific notations. Now I need to add them to a table with an insert query.

The query:
db.Execute ("INSERT INTO tmpZscore (datum, leeftijd, lengte, ZscoreL, gewicht, ZscoreW, BMI, ZscoreB, IBW) VALUES('" & CStr(dag & "/" & maand & "/" & Jaar) & "','" & test & "','" & CStr(lengte) & "'," & CDbl(scoreL) & ",'" & CStr(gewicht) & "','" & CStr(scoreW) & "','" & CStr(bmi) & "','" & CStr(scoreB) & "','" & Mid(CStr(ibw), 1, 6) & "%');")

I had already casted everything to string because it was easier to store decimal numbers that way. But now I need to store them as numbers because of the scientific notation. Otherwise I've got a problem in displaying these numbers in a report.
It would give -6,32 instead of -0,00632. I need the second one but I can't reformat the number if I don't have it as a number ofcourse.

Second problem: I've got my numbers in normal european format (5.000,236). So a comma for decimal seperator and a point for displaying. Now I can't get this number written in a tabl because of the comma. But when I change my regional settings to a point for decimal seperator all my numeric values in my table aren't correct anymore. Anyone who's got a solution for this to store decimal values with comma in a tabel.


Jul 30 '07 #1
1 3325
Apperantly it wasn't so hard.

For the ones who are going to stumble on this problem:

Just keep working like you are, don't change any regional settings. This gets messed up when you have decimal numbers with a comma already stored in your database. (7,03 becomes 703 for example)

Secondly to store the numbers you make sure your fields are in Double Precission, and your numbers are of the type double.

Then when you want to insert them into the table you use:

INSERT INTO bleh (First) VALUES ('" & CDbl(numer) & "');

Note the single quote before and after the double quotes placed around the number. I don't know why for sure but this way SQL doesn't comes to complain about a wrong amount of fields.

I'm sure there are other ways to this but this work for me so I'm very happy now.


Jul 30 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

21 posts views Thread by Batista, Facundo | last post: by
7 posts views Thread by Carl G | last post: by
10 posts views Thread by David Casey | last post: by
3 posts views Thread by Tanuki | last post: by
6 posts views Thread by KevinD | last post: by
2 posts views Thread by Steve Summit | last post: by
9 posts views Thread by jeff M via .NET 247 | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.