Data format in UPDATE statement

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rferrari27 - 02 May 2005 14:20 GMT

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

Reply to this Message

Klatuu - 02 May 2005 17:01 GMT

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

> Hi all,
> I have a syntax error problem with an update query.
[quoted text clipped - 24 lines]
> Thanks in advance for any responses.
> Roberto

Reply to this Message

rferrari27 - 03 May 2005 09:40 GMT

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

> 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:
[quoted text clipped - 29 lines]
> > Thanks in advance for any responses.
> > Roberto

Reply to this Message