SQL syntax error

[yanto]

Hi,
I got this error ("syntax error") when execute my SQL statement
(UPDATE tblPrice SET UnitPrice = " & curPrice WHERE ProductID=25) in
my client, I figured out that the regional setting trigger this error,
because curPrice variable contain 13,45 (so they use coma instead of
point = 13.45) follow the local regionall setting. How to overcome
this issue? I still want to use local regional setting.
TIA
Yanto

 

[Stefan Hoffmann]

hi,

I got this error ("syntax error") when execute my SQL statement
(UPDATE tblPrice SET UnitPrice = " & curPrice WHERE ProductID=25) in
my client, I figured out that the regional setting trigger this error,
because curPrice variable contain 13,45 (so they use coma instead of
point = 13.45) follow the local regionall setting. How to overcome
this issue? I still want to use local regional setting.

Use

"... SET UnitPrice = " & Str(curPrice) & "..."



mfG
--> stefan <--

 

[Baz]

You have not explained the context. How are you trying to execute this
statement?

If you are doing it from within a code module, then you need something like
this:

CurrentDb.Execute "UPDATE tblPrice SET UnitPrice = " & curPrice & " WHERE
ProductID=25"

If curPrice is a variable of type Currency then it does not mattrer what the
regional settings are with regard to decimal point.

 

[Stuart McCall]

Hi,
I got this error ("syntax error") when execute my SQL statement
(UPDATE tblPrice SET UnitPrice = " & curPrice WHERE ProductID=25) in
my client, I figured out that the regional setting trigger this error,
because curPrice variable contain 13,45 (so they use coma instead of
point = 13.45) follow the local regionall setting. How to overcome
this issue? I still want to use local regional setting.
TIA
Yanto

Place this code just before you build your SQL string:

curPrice = Val(Replace(Cstr(curPrice), ",", "."))

So if the user types a comma, it is converted to a period before being
evaluated.

 

[Baz]

Place this code just before you build your SQL string:

curPrice = Val(Replace(Cstr(curPrice), ",", "."))

So if the user types a comma, it is converted to a period before being
evaluated.

What??? If (as it appears to be) curPrice is a variable of type Currency
then all you are doing here is setting it to itself via a needless
conversion to String and then back to Currency.

 

[Stuart McCall]

What??? If (as it appears to be) curPrice is a variable of type Currency

then all you are doing here is setting it to itself via a needless
conversion to String and then back to Currency.

Brain fart. Thanks for catching.

 

[Stuart McCall]

Please ignore my previous. It is rubbish.

 

[Baz]

Brain fart. Thanks for catching.

No worries. The OP did mislead by misdiagnosing his own problem.

 

[yanto]

Hi, Thanks for any response.

my code are:

strSQL="UPDATE tblPrice SET UnitPrice = " & [curPrice] & " WHERE
ProductID=25"
dbs.execute strSQL,dbFailOnError

when I debuged the code the content of strSQL is "UPDATE tblPrice
SET UnitPrice = 13,45 WHERE ProductID=25"
and I got an error.
curPrice is textbox control
The regional setting is using comma instead of point (US decimal
format)
TIA
Yanto

 

[Stefan Hoffmann]

hi,

and I got an error.
curPrice is textbox control
The regional setting is using comma instead of point (US decimal
format)

Aha, how should the string (text) know that it is a currency value?

You have to convert it first:

".. SET UnitPrice = " & CCur(curPrice.Text) & " .."

And take a look at the Replace() function.


mfG
--> stefan <--

 

[yanto]

Hi Stefan,
Thanks for your response, adding CCur function gives no effect, stilll
error. if I use replace function to replace comma with point, then it
will be ok, but I have to detect the current regional setting. Is
there any best way to force the format of the currency allways in US
currency format when do an SQL executing no matter the regional
setting is?
TIA
Yanto

 

[yanto]

Hi, finaly I got a workarround from http://www.mvps.org/access/queries/qry0015.htm
so my sql statement will be
"UPDATE tblPrice SET UnitPrice = CCur(""" & Format$([UnitPrice]) &
""") WHERE ProductID=25;"
Thanks for any response and comment.
Yanto