Data format in UPDATE statement

[Guest]

Hi all,
I have a syntax error problem with an update query.
Here is the piece of code:

dim strSql as String
dim res as variant

strSql = "UPDATE Result_TBL SET Result = " & Me.Res & " WHERE IDRes = "
& res

cnn.Execute strSql, retVal

The problem is the value of "Me.Res" (a form field): it is a single precision
data type with 2 decimal digits:
1. if it does not have any decimal digit, it's fine (e.g., Me.Res = 1);
2. if it has decimal digits I get a "syntax error in update statement"
(e.g., Me.Res = 1,22).

The problem is in the comma: the corresponding DB table field is a single
precision data type with 2 decimal digits.

I tried to cast "Me.Res" to a single precision data type (CSng()) without any
positive results.

How can I convert "1,23" into "1.23" before executing the update statement?

Thanks in advance for any responses.
Roberto

 

[Guest]

Why is the comma there? Wouldn't it be better to use an input mask for your
text box that would give you the 1.23? If you really must do this, then:
mid(MyStr,instr(MyStr,","),1) = "."
If MyStr was 1,23 it will become 1.23

 

[Guest]

Hi Klatuu,
thank you for your response.
The input field is connected to a DB table field: it is a single precision
data type
with two decimal digits (fixed). Input masks are only allowed for fields whose
data type is string, date (not for numeric one), that's why I did not use it.
As a consequence, comma is there because Access does it (in Europe we
don't use dot notation for decimal digits).
rf