Update access via SQL?

I am trying to upadate and access db with SQL and I keep running into Update syntax error?  

Here is the code I am using

           


           sqlqry = "UPDATE eurusd SET HIGH =" + Str(vHigh) + ", LOW =" + Str(vLow) + ", OPEN =" + Str(vOpen) + ", CLOSE =" + Str(vClose) + ", TIME =" + Str(vTime) + " WHERE ID =" + Str(ry)
           Set RS = Conn.Execute(sqlqry)


when I print the string out eveything seems to be fine? Not sure what I am doing wrong here?
LVL 1
bliesveldAsked:
Who is Participating?
 
ShauliConnect With a Mentor Commented:
Is there a host somewhere near here? We have a lot of questions... :)

S
0
 
[ fanpages ]IT Services ConsultantCommented:
Hi,

Should you values for HIGH, LOW, OPEN, CLOSE, etc be in quotation marks?

e.g.

UPDATE eurusd SET HIGH = "something", LOW = "something else" WHERE ID=7

or even
0
 
[ fanpages ]IT Services ConsultantCommented:
UPDATE eurusd SET HIGH = "something", LOW = "something else" WHERE ID="7"

?

(Sorry, I accidentally submitted before I'd finished typing)
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
ShauliCommented:
All non-numeric fields should be surrounded by single quotes. All dates should be surrounded by # sign for access db, or single quote for sql server. All numeric fields should not besurrounded at all:

sqlqry = "UPDATE eurusd SET HIGH ='" & Str(vHigh) & "', LOW ='" & Str(vLow) & "', OPEN ='" & Str(vOpen) & "', CLOSE ='" & Str(vClose) & "', TIME ='" & Str(vTime) & "' WHERE ID ='"  & Str(ry) & "'"

S


0
 
bliesveldAuthor Commented:

- Shauli

I updated my sql string to fit the access syntax and still get the same error?  

New string

sqlqry = "UPDATE eurusd SET HIGH = " & vHigh & ", LOW = " & vLow & ", OPEN = " & vOpen & ", CLOSE = " & vClose & ", TIME = #" & vTime & "# WHERE ID =" & ry
0
 
PePiCommented:
i assume that all the variables are numeric except for vTIME? and are all the other fields numeric too? except for vClose of course which is date/time

if so, then:

sqlqry = "UPDATE eurusd SET HIGH = " & vHigh & ", LOW = " & vLow & ", OPEN = " & vOpen & ", CLOSE = " & vClose & ", TIME = " & CDate(vTime) & " WHERE ID =" & ry


i like using the CDate function, just my preference.
0
 
ShauliCommented:
I take it that HIGH, LOW, OPEN, CLOSE and ID are all numeric fields?

S
0
 
ShauliCommented:
PePi, now you drew first :)

S
0
 
bliesveldAuthor Commented:
yes they are.
0
 
PePiCommented:
lol!
0
 
[ fanpages ]IT Services ConsultantCommented:
sqlqry = "UPDATE eurusd SET HIGH =" + Str(vHigh) + ", LOW =" + Str(vLow) + ", OPEN =" + Str(vOpen) + ", CLOSE =" + Str(vClose) + ", TIME =#" + Format$(vTime,"d-mmm-yyyy") + "# WHERE ID =" + Str(ry)
           Set RS = Conn.Execute(sqlqry)
0
 
ShauliCommented:
Hold the horses, guys, TIME is a built in function. You know 'myTime=Time()'...
So, first question would be, is TIME a DATE type field? and the second one would be to change the field name to something like aTime, or bTime, just not TIME.

Let us know,

S
0
 
PePiCommented:
CLOSE is numeric not of date/time datatype?
0
 
ShauliCommented:
Now when you ask PePi, Open and Close are also built in functions...


S
0
 
PePiCommented:
AHA! I think Shauli has something. He's correct, either change the name of the field TIME to something else or enclose it in brackets "[]" like ...., [TIME] = " ....
0
 
PePiConnect With a Mentor Commented:
lol. yes they are. and one more thing. if CLOSE is suppose to be a date/time, shouldn't OPEN be one too?
0
 
bliesveldAuthor Commented:
Yup that fixed it....
0
 
ShauliCommented:
Way to go, I like it... :)

S
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.