reducing value to 2 decimals

[sven2]

Hello,

how can I make sure that when a user write 12,4423 in a independent field this value change into 12,44 (with vba).

Is this possible?

Thanks in advance,
Sven.

 

[pbaldy]

You could use the Round function.

 

[sven2]

Hello,

I tried it like this:

me.txttesting = round (me.txttesting,2)

but this doesn't do the trick ...

Sven.

 

[pbaldy]

Presuming that extra space wasn't in there, that looks okay. Where do you have it?

 

[sven2]

in the after update event of the field

 

[pbaldy]

I just tested and this worked as expected:

Private Sub txtLast_AfterUpdate()
  Me.txtLast = Round(Me.txtLast, 2)
End Sub

 

[sven2]

Hello,

indeed, you are right ... it works fine on a testcase but not in my application it isn't. Very strange???

I am working with an adp project and I want to fill a table. Before I run the SQL I want to make sure that the layout of the number is correct. I mean use a dot and not more than 2 decimals.

the code:

Private Sub txtwaarde_AfterUpdate()
Me.txtwaarde = Round(Me.txtwaarde, 2)
Me.txtwaarde = Replace(Me.txtwaarde, ",", ".")
End Sub

After that there is a button to save the value with this SQL:

strSQL = " INSERT INTO"
strSQL = strSQL & " TblKwaliteitswaarde ( Moederrolnummer, Kwaliteitswaarde, KwaliteitID )"
strSQL = strSQL & " VALUES(" & [Forms]![FrmIngaveparametersfrq]![Txtmoederrolnr] & ","
strSQL = strSQL & "" & [Forms]![FrmIngaveparametersfrq]![txtwaarde] & "," & [Forms]![FrmIngaveparametersfrq]![txtkwaliteitID] & ")"
CurrentProject.connection.Execute (strSQL)

When I show a msgbox of the SQL the dot is missing and suddenly 12.4412 becomes 124412

When I remove the round function everything works fine.

I haven't got any idea what is wrong?

Best regards,
Sven.

 

[sven2]

Hello,

i have done some testing and when you put immediately a dot in the number it goes wrong. (See example). What is the reason that this goes wrong?

Best regards,
Sven.

 

[Rabbie]

Sven, Is the field a text field or a numeric field. That might make a difference. Also is your Windows set up to use . or , as the decimal separator? It seems to work bst if everthing is set up consistently

 

[pbaldy]

I wonder if it's a regional settings problem. That works fine for me. I tried numbers like

123.456

and

.1234

each was correctly rounded to 2 digits.

 

[Rabbie]

I wonder if it's a regional settings problem. That works fine for me. I tried numbers like

123.456

and

.1234

each was correctly rounded to 2 digits.

Paul, I tested the posted DB with the same results. When you tested were you using a text field or a numeric field. Alo I was using A2007. Which version did you use

 

[sven2]

Hello,

about the field, it is an independent field and I am using access 2002 SP3.
When I am using "," it works fine ... when I am using a "." it goes wrong ...

 

[pbaldy]

Paul, I tested the posted DB with the same results. When you tested were you using a text field or a numeric field. Alo I was using A2007. Which version did you use

I tested with the OP's sample db, which failed for them, so I don't think the field type is relevant (and I already deleted the test db, so I don't know what it was). I tested with 2000, but I do have 2007 available if you think that may be the problem. My gut tells me it's a regional settings issue, but since all my applications are in the US I don't really have much experience with differing settings, so I'll yield to your experience there.