Storing and reading decimal numbers

[Twanne]

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

[Twanne]

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