Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to store decimal numbers in an Access database?

Status
Not open for further replies.

leifoet

Technical User
Jan 31, 2016
203
BE
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.
 
> if I type 1234,56

So, are we entering 1234.56 or 1234,56. If the latter, and "," is the grouping separator then this will be understood to be 123456, which is what you are suggesting you are seeing.
 
@ strongm

When typing a decimal directly into my Access database, there is indeed a different result depending on the separator used : "," versus "."
typing 1234,56 gives 1234,56 but typing 1234.56 gives 123 456.00 in the table - Sorry Andrzejek previously overlooked this distinction.

When saving an input from the HTLM Webform <Input ...> I always get 123 456.00 regardless of the Input separator used a comma or point.
Would this mean that for all decimal numbers entered via the <Input ...> separator are always converted to "." and so are incorrectly displayed in the table as '123 456.00' ?

Solution ?
Is it possible to add in HTML <Input ...> a code that converts every decimal input separator to ","? or something else ?


Thanks for tips.
 
It looks like there may be a difference between the web server and Access database regarding decimal indicators.

The next thing I would try is to use a regular input box, not number. This might push the "," through to MS Access.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
 
>typing 1234,56 gives 1234,56 but typing 1234.56 gives 123 456.00 in the table

Ok, so this suggests that Access is using "." as grouping seperator, and "," as decimal.

As dhookum suggest it looks like you have a discrepancy here between the database and the web server
 
I have tried to solve the problem of the decimal separation factor (point or comma) as follows :
of each input (XYZ) the decimal point is replaced by a decimal comma with the 'replace' function before storage.
As a result, all amounts are now stored in the Access database as 1234.56 regardless of the initial separator used (point or comma).

Exactly as it should be!

Thanks to dhookom, Andrzejek and strongm for the valuable input on this matter.
 
Cheered too early - still a problem popped up presumably in this line of code

<INPUT TYPE="number" step="0.01" max="9999.99" value="<%=rs("amount")%>" NAME="amount">

At the start of the application, 'value' always shows the already saved amount from the Access database in the input box (usually this is still blank).
Issue :
a stored decimal amount for example 1234.56 is not shown (box remains blank)
a stored decimal amount for example 1234.00 is shown as 1234 - ditto for a stored integer of 1234

When I test a 'decimal' <%response. Write rs("amount")%> (from the database), it is displayed perfectly.

Thanks for suggestions.
 
I would post a plea in the TT Active Server Pages forum to review this thread. I expect there is more experience there for handling this type of issue.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
 
I am with Duane.
You are 'outside' of Access' world at this moment, now you have just HTML issue(s) left to consider.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top