Access Sql Code syntax (1 Viewer)

[cyd44]

Could some please help with a syntax error in my VBA Sql code as follows:-

strSQL = "UPDATE [patient] " _
         & "SET [Current_Med] = '" & MyMed & "', _
         & [SType1_Desc] = '" & Me.SType_1 & "', _
         & [SType2_Desc] = '" & Me.SType2 & "', _
         & [SType3_Desc] = '" & Me.Stype3 "'" _
         & "WHERE  ([UID] = '" & Me.Web_UID & "')"

All above fields are text fields

 

[Rat1sully]

this might work i find it easier to write SQL on a single line or build the statement in parts then you don't miss any of the quotation marks as you'll get a compile error

strSQL = "UPDATE [patient] SET ([patient].[Current_Med] = '" & MyMed & "', [patient].[SType1_Desc] = '" & Me.SType_1 & "', [patient].[SType2_Desc] = '" & Me.SType2 & "', [patient].[SType3_Desc] = '" & Me.Stype3 & "') WHERE  ([UID] = '" & Me.Web_UID & "')"

 

[cyd44]

Thanks Rat1

I agree about the 1 line coding as I always get confused trying to code over multiple lines. It does look cleaner however and more readable. I have cut and pasted your suggestion but now get and error on the Docmd.RunSQL(strSQL)

 

[cyd44]

Manged to get it working by updating 1 field at a time. It is on 1 line and would appreciate advice on how to make it multiple lines using the & _. Wotking Code is

strSQL = "UPDATE [patient] " & _
         "SET [Current_Med] = '" & MyMed & "', [SType1_Desc] = '" & Me.SType_1 & "', [SType2_Desc] = '" & Me.SType2 & "', [SType3_Desc] = '" & Me.Stype3 & "' WHERE  [UID] = '" & Me.Web_UID & "' "

Thanks in advance

 

[JANR]

It is on 1 line and would appreciate advice on how to make it multiple lines using the & _.

Somthing like this:

strSQL = " UPDATE [patient]" & _
         " SET [Current_Med] = '" & MyMed & "'," & _
         " [SType1_Desc] = '" & Me.SType_1 & "'," & _
         " [SType2_Desc] = '" & Me.SType2 & "'," & _
         " [SType3_Desc] = '" & Me.Stype3 & "'" & _
         " WHERE ([UID] = '" & Me.Web_UID & "')"

JR

 

[mdlueck]

Or you may totally avoid having to correctly pass in variables and embed them in the SQL string by using...

Example of SQL INSERT / UPDATE using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=219149

 

[Rat1sully]

If I'm going to split it into lines I tend to do it like this, it always goes horribly wrong and takes an age to debug when I use _

strSQL = " UPDATE [patient] " 
strSQL = strSQL + "SET [Current_Med] = '" & MyMed & "', "         
strSQL = strSQL + "[SType1_Desc] = '" & Me.SType_1 & "', "
strSQL = strSQL + "[SType2_Desc] = '" & Me.SType2 & "', "
strSQL = strSQL + "[SType3_Desc] = '" & Me.Stype3 & "' " 
strSQL = strSQL + "WHERE ([UID] = '" & Me.Web_UID & "');"

also JR appears to have missed the semicolon off the end

 

[mdlueck]

also JR appears to have missed the semicolon off the end

Usually semicolons are not required.

Via an ODBC connection to DB2/400 running on an AS/400 iSeries, a semicolon at the end of a SQL is rejected by DB2/400.

 

[BigHappyDaddy]

Minor modification of JR's example:

strSQL = "UPDATE [patient] " & _
         "SET [Current_Med] = '" & MyMed & "', " & _
             "[SType1_Desc] = '" & Me.SType_1 & "', " & _
             "[SType2_Desc] = '" & Me.SType2 & "', " & _
             "[SType3_Desc] = '" & Me.Stype3 & "' " & _
         "WHERE ([UID] = '" & Me.Web_UID & "');"

Basically I put the <space> at the end of each line, right before the closing quotation mark. I also indent each 'sub' line that is related to the SQL keyword. In this example, the three lines after the inital SET line are indented and lined up. Same concept for each SQL keyword that has multiple lines. For me it makes it much easier to read.