How to store decimal numbers in an Access database?

[leifoet]

The storage of numbers with 2 decimals from the HTML- inputform in the Access database fails
=> decimal digits of the entered number are placed before the decimal point in the field xyz (followed by 00 after the decimal point).

Input codes (in the form)
<INPUT TYPE="number" step="0.01" placeholder="1.0" value="<%=rs("xyz")%>" NAME="xyz">

Field settings (Access field xyz)
-Filed size : double precision
-Format : general
-Decimal Places : 2
-Input Mask : (none)

How can I solve this? - Thanks for tips.

 

[dhookom]

Do you realize this forum is for MS Access forms, not HTML forms?

I'm not sure if the initial display doesn't have decimals or if the result of the input doesn't. You can test some inputs at this web page.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[leifoet]

dhookom, tests in the suggested 'input step attribute' do not provide useful elements for a solution to my problem.
When inputting decimal numbers with both decimal point and decimal comma I only get the message that the number must be an integer.
I believe this link is only intended as a simple storage example in the PHP course and can only handle integers but not my decimal number problem (preferably with decimal comma).

By the way, my submitted problem lies in my opinion in 3 areas:
1. my program is written in ASP Classic (cfr. <% ... %>
2. using the HTML input element
3. the input is stored in an Access database (to be used further in the program)
Only the storage of decimal numbers fails; the storage of integers and text (strings) works without any problems.

Which forum should I move my problem to?
Thanks for tips.

 

[dhookom]

I assume you have opened the Access db directly and attempted to enter a decimal number in the field. It's been a lot of years since I worked in classic ASP. If you are creating an SQL statement, I would write it to the screen for debugging.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[leifoet]

Opening the database and updating the data in it is done via the post method which passes the input of the data automatically from the input form to the database.

Only the storage of decimal numbers fails; the storage of integers and text (strings) works without any problems.
Could the national decimal separator (point versus comma) sometimes be the cause?
How to solve ?

For example, split the input of the number 123.45 into input of '123'+','+'45' ?
But how to code?

 

[dhookom]

I wasn’t aware a Post would automatically connect to a database without some code to execute an insert or update statement.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

When inputting decimal numbers with both decimal point and decimal comma I only get the message that the number must be an integer.

Is that the Access' message?
If so, looks like the field in the table is not declared correctly.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[leifoet]

@dhookom : an update query is required to save the changed fields in the database - strings and integers are saved with this without any problems

But maybe the problem is in the wrong declaration

@Andrzejek : can you help me with the correct declaration of the field of the table in which the decimal numbers are written.

Thanks for help.

 

[Andrzejek]

Try this way

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[dhookom]

You haven’t shared the code for the update query. Did you try use Response.Write to display the update query on the screen?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[leifoet]

Andrzejek : tried other field settings e.g. decimal - no solution to the decimal problem

dhookom : sorry, I misunderstood your question.

Update query :

'record number to be updated
strSQL = "SELECT * FROM Ch22 WHERE Nr=" & lngRecordNo  

rsUpdateEntry.CursorType = 2
rsUpdateEntry.LockType = 3

rsUpdateEntry.Open strSQL, adoCon

'txt fields
rsUpdateEntry.Fields("team1") = Request.Form("team1")
... 
rsUpdateEntry.Fields("Payment") = Request.Form("payment")

'number field => my decimal problem field !
rsUpdateEntry.Fields("XYZ") = Request.Form("XYZ")

rsUpdateEntry.Update

Debugging with response.write : no experience with it
How do I start to see what happens to the XYZ field?

Thanks

 

[strongm]

This should do it. And don't ask why we are using CDbl rather than CDec ...

[tt]rsUpdateEntry.Fields("XYZ") = CDbl(Request.Form("XYZ"))[/tt]

 

[Andrzejek]

I would use:

[pre]
strSQL = "UPDATE Ch22 Set" & _
" team1 = '" & Request.Form("team1") & "', " & _
" Payment = '" & Request.Form("payment") & "', " & _
" XYZ = " & CDbl(Request.Form("XYZ")) & _
" WHERE Nr = " & lngRecordNo

adoCon.Execute strSQL
[/pre]
But that's just a personal preference...

(Yes, I know. SQL Injection issue...)

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[leifoet]

Andrzjek, I tested/used your code (cfr. above) - with the following (initial) settings for the Access field XYZ :
-Filed size : double precision
-Format : general
-Decimal Places : 2
-Input Mask : (none)

Whatever I input, for example 1234.56 or 1234.56, the storage always remains 123 456.00.

If I set the 'decimals' in the Access settings to 0 I still get 123 445.00 => with 2 decimals.

Possibly a wrong input line in the form => <INPUT TYPE="number" step="0.01" placeholder="1.0" value="<%=rs("xyz")%>" NAME="xyz">
Its output seems OK anyway - I tested the Request.Form("XYZ") from the input line before storing it in the database - shows exactly what I entered.

The question remains : how can the number 1234.56 be distorted to 123 456.00 when sending to the server / database ?

 

[Andrzejek]

>Whatever I input, for example 1234.56 or 1234.56, the storage always remains 123 456.00.
Do you do that straight in Access? By-passing HTML all together?

Because we still don't know if you enter 1234.56 in Access, you get/see 1234.56 in Access.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[leifoet]

My normal procedure is always the same (also in these messages): via the html code <Input type="number" ...>" in my program, I enter the amount XYZ, for example 1234.56 ... with the known storage result in the Access table Ch22 : 123 456.00 ;-(
On the other hand, if I type 1234,56 directly into the appropriate field XYZ of the appropriate Access table Ch22, then 1234,56 does not change in the table.

I guess that was the question?

 

[Andrzejek]

So, now we know Ch22 table is capable to accept and keep the value of 1234,56 - good.

Did you try strongm's suggestion of:
[tt]rsUpdateEntry.Fields("XYZ") = [red]CDbl([/red]Request.Form("XYZ")[red])[/red][/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[leifoet]

Yes, I have tested strongm's suggestion separately, but with no improvement in results => 123 456.00
By the way, this suggestion was already included in your code anyway.

 

[dhookom]

I would try change the request for the numeric from the form to a hard-coded number. It might provide a clue.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[strongm]

>-Format : general
-Decimal Places : 2

By the way, setting these does not change the way the data is stored, just the way certain parts of Access display the data (and even in those, General format ignores the Decimals setting). A Double is always stored in IEEE 754 double-precision binary floating-point format.