0

I need help regarding the WHERE clause of an SQL statement to UPDATE field values [olnAg] in a query (qryCLFull same as tblCLFull). The field [olnAg] should be updated to the second text of [cellLine] field. So for example if [cellLine] has "

This is the cellLine field

[olnAg] should have

is

Getting the recordset and values has been successful with the code below (with the click of a button on frmSecWord but the issue with UPDATE of the [olnAg] field.

Private Sub btnBySpace_Click() 
    Dim rsON As DAO.Recordset 
    Dim rsNN As DAO.Recordset 
    Dim db2 As Database 
    Dim sqlON As String 
    Dim sqlNN As String 
     
    Dim newVal As String 
    Dim arr As Variant 
 
    sqlON = " SELECT  cellLine FROM  qryCLFull" 
    sqlNN = " SELECT  olnAg FROM  qryCLFull" 
 
    Set db2 = CurrentDb 
     
    Set rsON = db2.OpenRecordset(sqlON)  
    Set rsNN = db2.OpenRecordset(sqlNN) 
    arr = Split(rsON!cellline, " ") 
     
    If IsNull(arr(1)) Then 
    newVal = arr(0) 
    Else: newVal = arr(1) 
    End If 
 
    Do Until rsON.EOF 
    If Not IsNull(rsON.Fields(0).Value) Then 
 
    sqlstr = "UPDATE qryCLFull SET qryCLFull.olnAg = '" & newVal & "' 
    db2.Execute sqlstr 
    End If 
     
    rsON.MoveNext 
    rsNN.MoveNext 
   
    Loop 
 
    rsON.Close 
    rsNN.Close 
     
    Set rsNN = Nothing 
    Set rsNN = Nothing 
    Set db2 = Nothing 
     
End Sub

enter image description here

Now with this UPDATE and SQL code, all the records of [olNAg] are changed to "is" instead of records WHERE [CellLine] has the text "This is the cellLine field" I have tried severally to adjust the WHERE clause but I haven't been able to write a correct clause. This sql updates [olnAg] for all records in the table instead of just for instances where we find that particular rsON!CellLine. Help will be greatly appreciated.

MordC
  • 61
  • 3
  • 11
  • Please show sample data to see more examples of `newVal`? Can it ever contain a single quote? Try to `Debug.Print newVal` to output in Immediate Window (Ctrl+G) just before the update to see which value it errs on. – Parfait Jun 07 '22 at 00:46
  • Hello @Parfait. Everything works well. My problem is getting the UPDATE (on olnAg) WHERE [CellLine] = rsON.Fields(0).Value. **I do not know how to combine the strings and the ```sql``` with the ```WHERE``` clause**. I'm now typing from my Mac and do not have MS Access so I cannot share the debug prints. The error will be the as it is more of semantics issue than syntax. . – MordC Jun 07 '22 at 02:23
  • Without tabular data to illustrate (current input / desired output) I am having trouble understanding your issue. Where are you attempts at the `WHERE` clause? – Parfait Jun 07 '22 at 19:35

0 Answers0