Help tracking down a syntax error 2

[timoteo]

I am trying to use the Execute Method to update a table and I keep getting an syntax error message for my update statement. It's drining me crazy, I've gone over the code a dozen times and I can't find where I am going wrong. Can anyone help me find the problem. Here is my code:


strMemoSQL = "Update Memo " & _
"Set Memo = '" & txtMemo.Text & "' Where AcctNum=" & Val(txtAcctNum.Text)

conMemo.Execute strMemoSQL, , adCmdText

Thank you in advance for any assistance.

Timoteo

 

[johnwm]

Is Memo your table name and your field name?

Syntax for Update is:

Update myTable Set myField = Expression Where whatever
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[timoteo]

johnwm,

Yes, the field and the table are named the same. Do they need to be named different?

 

[zemp]

I would say yes becuase the database needs to be able to differenciate between the two names. Something as simple as giving the table a prefix such as 'tblMemo'. Thanks and Good Luck!

zemp

 

[timoteo]

Zemp,

I tried changing the name of the table and VB still tells me I have a syntax error in my Update statement.

This one is really driving me crazy.

Thanks for your suggestion.


Timoteo

 

[zemp]

Which error are you getting? What database are you using?

Also, sometimes I found that the line continuation character can cause problems. I try and build my SQL statements this way,

strMemoSQL = "Update Memo "
strMemoSQL =strMemoSQL & "Set Memo = '" & txtMemo.Text & "' Where AcctNum=" & Val(txtAcctNum.Text)

Another thing you can try is to place a debug.print statement in your code so you know exactly what the SQL statement is.

Debug.Print strMemoSQL

Also, make sure you are passing the correct data types.

If I am having trouble with an SQL statement I like to build one in the database that works and compare the two. Sometimes I see the error I made.

Thanks and Good Luck!

zemp

 

[johnwm]

1. Was your code cut&pasted from code to post - I notice there are no spaces either side of the '=' sign:

"Set Memo = '" & txtMemo.Text & "' Where AcctNum=" & Val(txtAcctNum.Text)

Should be:

"Set Memo = '" & txtMemo.Text & "' Where AcctNum = " & Val(txtAcctNum.Text)

2. Sounds silly, but is AcctNum a numeric field?
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[timoteo]

Zemp,

The exact error message is:

Run-time error '-2147217900 (80040e14)':

Syntax Error in UPDATE statment.

I'm connecting to an Access 2000 database using Jet 4.0 OLE DB.

I will try your other suggestions and let you know.

Thanks for the help.

johnwm,

At first, I did'nt have the space, but even after changed it, I still received the syntax error.

AcctNum is a numeric field.

Thanks again for help. I'll let you know how it goes.


Timoteo

 

[johnwm]

As Zemp says Another thing you can try is to place a debug.print statement in your code so you know exactly what the SQL statement is.

Debug.Print strMemoSQL

Copy and paste the exact result from that into your next post so we can (hopefully) see the problem
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[timoteo]

Zemp, johnwm

Here is the results of the Debug.Print:

Update MemoTable Set Memo = 'United Way Donor' Where AcctNum = 14857

Using the following code:

strMemoSQL = "Update MemoTable " & _
"Set Memo = '" & txtMemo.Text & "' " & _
"Where AcctNum = " & txtAcctNum.Text
Debug.Print strMemoSQL
conMemo.Execute strMemoSQL, , adCmdText

Everything seems right to me. But still the syntax error?!?

I can't thank the both of you enough for your help.

Timoteo

 

[zemp]

Two more things you can try.

Place a semicolon at the end of the statement. Access seems to like this.

"Where AcctNum = " & txtAcctNum.Text & ";"

The second is to place square brackets around all table and field names.

"Where [AcctNum] = " & txtAcctNum.Text & ";"
Thanks and Good Luck!

zemp

 

[Guest_imported]

Zemp,

I think "Memo" may be a reserved word. Try coding your query string so that it outputs this:

UPDATE MemoTable SET [Memo] = 'United Way Donor'
WHERE AcctNum=14857;

I've tested this on a table and SQL query set up in Access 2000. Lee
lee.meinhardt@smna.com
The trouble with resisting temptation is it may never return again...

 

[timoteo]

zemp, rangerlee:

You guys were right on. "Memo" must be a reserved word because as soon as I put the brackets around it, the code executed perfectly.

Thanks to eveyone who posted a tip. I feel as though a great weight has been lifted off my shoulders...


Timoteo