docmd.runsql update problem

[Rotsj]

Hi,

i try to execute an update query from within a form, but i get the message:
run time error '3144'. Syntax error on the update statement


when i try something like this:
DoCmd.RunSQL "UPDATE tblKlant " & _
"SET tblKlant.Bedrag = tblKlant.Bedrag + 10.96" & _
" WHERE tblKlant.[Klant-id] = forms![Form1]![Klant-id]"
everything works fine. If i change it to the following:
Dim ntest As Double
ntest = 10.96
DoCmd.RunSQL "UPDATE tblKlant " & _
"SET tblKlant.Bedrag = tblKlant.Bedrag + " & ntest & _
" WHERE tblKlant.[Klant-id] = forms![Form1]![Klant-id]"
i get the error. If i change the value of ntest to 10, there's no problem...

I don't understand this, can anyone help?

Thnx.
Rogier

[Mike Storr]

Re: docmd.runsql update problem

Try using Debug.Print to send the whole SQL statement to the Debug window,
and post it again, just to see what the result is coming out as. I can't see
any reason there that it would fail.

Mike Storr

https://www.veraccess.com

"Rotsj" <r.knipscheer@h ome.nl> wrote in message
news:f40a22ed.0 401210526.6e217 3be@posting.goo gle.com...[color=blue]
> Hi,
>
> i try to execute an update query from within a form, but i get the[/color]
message:[color=blue]
> run time error '3144'. Syntax error on the update statement
>
>
> when i try something like this:
> DoCmd.RunSQL "UPDATE tblKlant " & _
> "SET tblKlant.Bedrag = tblKlant.Bedrag + 10.96" & _
> " WHERE tblKlant.[Klant-id] = forms![Form1]![Klant-id]"
> everything works fine. If i change it to the following:
> Dim ntest As Double
> ntest = 10.96
> DoCmd.RunSQL "UPDATE tblKlant " & _
> "SET tblKlant.Bedrag = tblKlant.Bedrag + " & ntest & _
> " WHERE tblKlant.[Klant-id] = forms![Form1]![Klant-id]"
> i get the error. If i change the value of ntest to 10, there's no[/color]
problem...[color=blue]
>
> I don't understand this, can anyone help?
>
> Thnx.
> Rogier[/color]

[rogier knipscheer]

Re: docmd.runsql update problem

Hello Mike,

thanks for your advice, now i know what goes wrong:
ntestje = 10.5

debug.Print "UPDATE tblKlant " & _
"SET tblKlant.Bedrag = tblKlant.Bedrag + " &
ntestje & _
" WHERE tblKlant.[Klant-id] =
forms![Form1]![Klant-id]"
UPDATE tblKlant SET tblKlant.Bedrag = tblKlant.Bedrag + 10,5 WHERE
tblKlant.[Klant-id] = forms![Form1]![Klant-id]


as you see, instead of 10.5, it translates into 10,5. Do you know what
to do to solve this?

Thanks!
Rogier



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Mike Storr]

Re: docmd.runsql update problem

The only reason I can think of why it would use a comma is because of the
Regional settings of the computer, but this should only be a format and not
change the data.
My only suggestions are to check the settings in control panel for decimal
format, try using Single instead of Double, build the query using design
view - then switch to SQL view and copy and paste the SQL into your code and
see if it looks any different - or works any different. Beyond that I'm out
of ideas.


Mike Storr

https://www.veraccess.com

"rogier knipscheer" <r.knipscheer@h ome.nl> wrote in message
news:400ed323$0 $70307$75868355 @news.frii.net. ..[color=blue]
> Hello Mike,
>
> thanks for your advice, now i know what goes wrong:
> ntestje = 10.5
>
> debug.Print "UPDATE tblKlant " & _
> "SET tblKlant.Bedrag = tblKlant.Bedrag + " &
> ntestje & _
> " WHERE tblKlant.[Klant-id] =
> forms![Form1]![Klant-id]"
> UPDATE tblKlant SET tblKlant.Bedrag = tblKlant.Bedrag + 10,5 WHERE
> tblKlant.[Klant-id] = forms![Form1]![Klant-id]
>
>
> as you see, instead of 10.5, it translates into 10,5. Do you know what
> to do to solve this?
>
> Thanks!
> Rogier
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]

[Fletcher Arnold]

Re: docmd.runsql update problem

"rogier knipscheer" <r.knipscheer@h ome.nl> wrote in message
news:400ed323$0 $70307$75868355 @news.frii.net. ..[color=blue]
> Hello Mike,
>
> thanks for your advice, now i know what goes wrong:
> ntestje = 10.5
>
> debug.Print "UPDATE tblKlant " & _
> "SET tblKlant.Bedrag = tblKlant.Bedrag + " &
> ntestje & _
> " WHERE tblKlant.[Klant-id] =
> forms![Form1]![Klant-id]"
> UPDATE tblKlant SET tblKlant.Bedrag = tblKlant.Bedrag + 10,5 WHERE
> tblKlant.[Klant-id] = forms![Form1]![Klant-id]
>
>
> as you see, instead of 10.5, it translates into 10,5. Do you know what
> to do to solve this?
>
> Thanks!
> Rogier[/color]


Rogier
Consider also any thousand separator (I don't know if you write numbers like
the Germans). If you have a string like 1.724,5 (meaning one thousand seven
hundred and twenty-four and a half) you must first get rid of the "." for
the thousands and then replace the "," with "."

If you have Access 2000 or above, you can use the Replace function

strNumber = Replace(Format( varNumber), ",", ".")

Will return 1724.5

PS
There are other ways to execute SQL other than DoCmd.RunSQL. You could
write a function which would tell you how many records had been updated by
the change (perhaps this should always be one).

Set dbs = CurrentDb
dbs.Execute strSQL, dbFailOnError
Msgbox dbs.RecordsAffe cted & " record(s) updated"
Set dbs = Nothing