SQL syntax error

  • Thread starter Thread starter yanto
  • Start date Start date
Y

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
 
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.
Click to expand...
Use

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



mfG
--> stefan <--
 
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.
 
yanto said:
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
Click to expand...

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.
 
Stuart McCall said:
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.
Click to expand...

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.
 
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.
Click to expand...

Brain fart. Thanks for catching.
 
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
 
hi,
and I got an error.
curPrice is textbox control
The regional setting is using comma instead of point (US decimal
format)
Click to expand...
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 <--
 
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
 
Back
Top