Update a table with a whereclause

you had double quotes for the MU ...

Docmd RunSQL sSQL ="Update TableA set Field1 = 'LT' where TableA.TypeID = 'MU' And TableA.SizeID <= 2"

The field SizeID is the problem ...it cannot pull the field for the test...I believe the field should be in decimal places before the query can work.

how about this

Docmd RunSQL "Update TableA set Field1 = 'LT' where TableA.TypeID = 'MU' And TableA.SizeID <= 2"

or this

sSQL ="Update TableA set Field1 = 'LT' where TableA.TypeID = 'MU' And TableA.SizeID <= 2"

Docmd RunSQL sSQL

how about this

Docmd RunSQL "Update TableA set Field1 = 'LT' where TableA.TypeID = 'MU' And TableA.SizeID <= '2'"

or this

sSQL ="Update TableA set Field1 = 'LT' where TableA.TypeID = 'MU' And TableA.SizeID <= '2'"

Docmd RunSQL sSQL

Recommend you create another table which contains unique sizeID values and their equivalent numeric values

SizeID     SizeDbl
3/4            .75
1              1.0
1-1/2        1.5
2               2.0

Then you can join this table to TableA on the SizeID field and use the [SizeDbl] field in your where clause

UPDATE Table A
INNER JOIN TableB
ON TableA.SizeID = TableB.SizeID
SET TableA.Field1 = "LT"
WHERE NZ([SizeDbl],3)<=2

This is another table that I created named tblSize:

Size      SizeID    SizeType
1/2                 2            0.5
3/4                 3            0.75
1            4          1
1-1/4             5            1.25
1-1/2             6            1.5
2                   7             2


I assume that the query should reference the SizeType in order for it to work

Size      SizeID    SizeType
1/2                 2            0.5
3/4                 3            0.75
1                    4            1
1-1/4             5            1.25
1-1/2             6            1.5
2                   7             2

fyed:
I tried your suggested idea as follows and I received a Run-time error '3615': Ty mismatch on expression ...
and debug highlighted.

        DoCmd.RunSQL sSQL

from:

 sSQL = "UPDATE TableA " & _
            "INNER JOIN TableB ON TableA.SizeID = TableB.SizeID " & _
            "Set TableA.Field1 = 'LT' WHERE Nz([TableB.SizeDbl]) <= 2;"

        DoCmd.RunSQL sSQL

fyed:

You were right. Your answer under ID:35241333 resolved the problem.

update with join, heh? :)
https://www.experts-exchange.com/A_1517.html

You might also want to consider using the currentdb.Execute method rather than docmd.RunSQL

With the Execute method, you provide a SQL statement to execute, and can include an optional paramter (dbFailOnError) which will cause an error to be raised if the SQL statement does not execute properly.  You can then use an Error handler to assist you in identifying the problem with your SQL statement.

fyed:
Is this what you are talking about? Could you please fine tune what I posted below.

(a). WHERE can I place this code..... "(dbFailOnError)"

(b). How about error handler here?

**************

Private Sub btnStep3_Click()
Dim SQLstr As String

  DoCmd.SetWarnings False
   
    SQLstr = "UPDATE Table " & _
                   "INNER JOIN TableB ON  " & _
                   "TableA.SizeID = TableB.Size " & _
                   "SET TableA.Field1 = 'LT' " & _
                   "WHERE [SizeType]<=2; "

    CurrentDb.Execute SQLstr
   
    Debug.Print (SQLstr)
   
        MsgBox "Updates on TableA Successful"
   
    DoCmd.SetWarnings True

End Sub

It would look something like:

Private Sub btnStep3_Click()
    Dim SQLstr As String

    On Error Goto ProcError       <------  Add this line to force code to the error handler if an error occurs

    Docmd.Setwarnings False    < -----  Delete this line, no longer needed when using Execute method

    SQLstr = "UPDATE Table " & _
                   "INNER JOIN TableB ON  " & _
                   "TableA.SizeID = TableB.Size " & _
                   "SET TableA.Field1 = 'LT' " & _
                   "WHERE [SizeType]<=2; "

    Debug.Print (SQLstr)
    CurrentDb.Execute SQLstr, dbFailOnError

    MsgBox "Updates on TableA Successful"

ProcExit:  
    Exit Sub    
    DoCmd.SetWarnings True  <--------------   Delete this line, no longer needed with Execute Method

ProcError:
    debug.print err.number, err.description
    debug.print "SQLstr:" & SQLstr
    msgbox err.number & vbcrlf & err.description, vbOkOnly, "Error in Update procedure"
End Sub

Thanks for the addiditonal info.

Quick question.

What if I process multiple strings will the Currentdb.Executive look like the one bellow?

............................
...........................

Dims sSQL1, sSQL2, sSQL3, sSQL4, sSQL5, sSQL6, sSQL7, sSQL8 As String
...........................
..........................
.........................

 CurrentDb.Execute  sSQL1, sSQL2, sSQL3, sSQL4, sSQL5, sSQL6, sSQL7, sSQL8, dbFailOnError



Also how would the error trapping changed?

ProcError:
    debug.print err.number, err.description
    debug.print "sSQL1:" & sSQL1, "sSQL2:" & sSQL2, "sSQL3:" & sSQL, ....etc


    msgbox err.number & vbcrlf & err.description, vbOkOnly, "Error in Update procedure"
End Sub