query syntax

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The following statement gets an error "incorrect syntax near 'AS' "

strSQLM = "UPDATE OccMalignant AS omg SET omg.AxSeroma = [cboAxSeroma], " & _
"omg.AxHematoma = [cboAxHematoma], omg.AxParesthesia =
[cboAxParesthesia], " & _
"omg.LymphEdema = [cboLymphEdema], omg.BrEdema = [cboBrEdema], " & _
"omg.IBnerveDivided = [chkIBNerveDivided], omg.IBnumb = [chkIBNumb]" & _
"WHERE omg.AilmentID= " & Me.AilmentID & ";"

What am I not seeing?
This is used in ado code connecting to SQL server.
Thanks so much.
 
You don't have a blank space in front of the WHERE word in your string
building.
 
Good eye. I corrected that. But I still have a syntax error. I am running
that query with this line:

rs.Open strSQLW, cnn, adOpenDynamic, adLockOptimistic

the recordset and connection strings are fine, they work elsewhere in code

Ken Snell said:
You don't have a blank space in front of the WHERE word in your string
building.

--

Ken Snell
<MS ACCESS MVP>

smk23 said:
The following statement gets an error "incorrect syntax near 'AS' "

strSQLM = "UPDATE OccMalignant AS omg SET omg.AxSeroma = [cboAxSeroma], " & _
"omg.AxHematoma = [cboAxHematoma], omg.AxParesthesia =
[cboAxParesthesia], " & _
"omg.LymphEdema = [cboLymphEdema], omg.BrEdema = [cboBrEdema], " & _
"omg.IBnerveDivided = [chkIBNerveDivided], omg.IBnumb = [chkIBNumb]" & _
"WHERE omg.AilmentID= " & Me.AilmentID & ";"

What am I not seeing?
This is used in ado code connecting to SQL server.
Thanks so much.
Click to expand...
Click to expand...
 
Are cboAxSeroma, cboAxHematoma, etc comboboxes on a form? If so, I think you
will have to get their values and insert that into your sql string instead of a
reference to the control - similar to what you have done in the WHERE clause
with Me.AilmentID.

Something like:

strSQLM = "UPDATE OccMalignant AS omg" & _
" SET omg.AxSeroma = '" & me.[cboAxSeroma] & "', " & _
"omg.AxHematoma = '" & me.[cboAxHematoma] & "', " & _
"omg.AxParesthesia ='" & ...
"WHERE omg.AilmentID= " & Me.AilmentID & ";"

If your values are numbers vice strings then you can drop the ' delimiters
 
Thanks all. I learned a couple of things that could be useful to others:
1) for Update queries, although Access accepts a table alias, SQL server
does not.
2) the table columns weren't happy until I dropped the table qualifier

so this finally worked:
strSQLM = "UPDATE OccMalignant SET AxSeroma = " & Me.cboAxSeroma & _
", AxHematoma = " & Me.cboAxHematoma & ", AxParesthesia = " &
Me.cboAxParesthesia & _
", LymphEdema = " & Me.cboLymphedema & ", BrEdema = " & Me.cboBrEdema & _
", IBnerveDivided = " & Me.chkIBNerveDivided & ", IBnumb = " &
Me.chkIBnumb & _
" WHERE OccMalignant.AilmentID= " & Me.AilmentID & ";"
 
Back
Top