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 4 10192
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 www.veraccess.com
"Rotsj" <r.**********@h ome.nl> wrote in message
news:f4******** *************** ***@posting.goo gle.com... 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
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!
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 www.veraccess.com
"rogier knipscheer" <r.**********@h ome.nl> wrote in message
news:40******** *************** @news.frii.net. .. 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!
"rogier knipscheer" <r.**********@h ome.nl> wrote in message
news:40******** *************** @news.frii.net. .. 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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Jim |
last post by:
I am using Access 97 on a PC running Windows NT 4.0 SP6a.
I have some code (shown below) intended to add a set of records to one
table (tblGradeProps) when a new record is created in another
(tblGrades)
The oddity is that when the procedure Detail_Click is called from the
procedure cmbMktSect_Exit, the RunSQL statement doesn't add the
required records but it does so when invoked by the Detail_Click
event!
|
by: Andy |
last post by:
Hello,
I am running an sql statement that INSERTS INTO a table. If I run the
query using docmd.runSQL, it works fine - new records are added to the
table and duplicate records are disregared ( I use DoCmd.SetWarnings
False so the users don't see the warning about duplicate records).
If I run the same query using this:
rs.Open qryPolePosition1, CurrentProject.Connection, adOpenKeyset,
|
by: Pathfinder |
last post by:
Hi All
I am trying to run the below but I receive the following error
"runsql action requires an argument consisting of an SQL statment"
Dim MySQL$
MySQL$ = "Select * from mytablename"
DoCmd.RunSQL MySQL$
Any reason for that
|
by: Richard Hollenbeck |
last post by:
Help! I don't know why this isn't working:
Private Sub Combo9_Change()
Dim UsersCourseSelection As String
UsersCourseSelection = Me("Combo9").Value
Combo13.Visible = True
'the following SQL thing is all on one line in the actual code.
|
by: RC |
last post by:
In my Access 2002 form, I have a combo box and on the AfterUpdate
event I use
DoCmd.RunSQL ("UPDATE ....
to update records in a table. When it starts to run I get a message
"You are about to update 3 row(s)."
Is there a way to prevent the message from popping up?
| |
by: ben |
last post by:
I have the following code in a VBA module:
DoCmd.RunSql "Update tData Set sd = Log(Strike/Price) where symbol = '"
& symbol & "'"
This statement worked fine, and was using the built in math Log
function.
In a separate module, I added the following function:
|
by: Tomba |
last post by:
I have a really annoying problem, which i tried solving for almost 3 days now. (I googled in any variation i could think of, but i couldn't find anything that will help my stupid me understand:\)
I'm trying to use a ALTER TABLE ALTER COULMN to change the datatype of a certain column in a table i'm importing by code from an .xls to my access database.
I'll cut to the chase and paste the code:
Option Compare Database
Option Explicit
...
|
by: jamesnkk |
last post by:
Hi, I try to run the below statement, it alway popup a box and prompt me to input the unit, what wrong with my statement. There is no sub form.
DoCmd.RunSQL "Update tblvndprod set unit= " & Me.UnitMeasurement & " where productID=" & Me.ProductID
|
by: natural |
last post by:
Good Afternoon
I have an option grou[ and on the after update i would like to provide the user with a msgbox, and then action
placed my docmd.setwarnings false everywhere, but i still get my "record has been deleted".
Is i possilbe to please assist in where i am going wrong
Private Sub RecOffLet_AfterUpdate()
On Error GoTo Err_Handler
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |