469,898 Members | 2,233 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,898 developers. It's quick & easy.

Storing and reading decimal numbers

65
Hi,

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.

Greetz

Twanne
Jul 30 '07 #1
1 3197
Twanne
65
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.

Greetz

Twanne
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
1 post views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.