Do nothing if criteria isn't met

[Thompyt]

I am updating a table with:
parsed down code

If rV![FieldA] = "Fires And rV![FiledB] = "Water" Then

For I = 1 to 4 
  J = 1 to 4
    K=CHR(64 + J)
     If rV![Field1] = i & "PPP " & K & "/RRR" Then
        Do whats needed
          Else if......
             Do whats needed
         Else: Do Nothing
      End If
   Next J
Next i
End If

This code will update anything after the criteria is met, even if it is not met. How do I reference the Else: Do Nothing?



Else: Next J?


Thanks

[June7]

Possibly:

If rV![FieldA] = "Fires And rV![FiledB] = "Water" Then
For i = 1 to 4 
  For J = 1 to 4
     K=CHR(64 + J)
     If rV![Field1] = i & "PPP " & K & "/RRR" Then
        Do whats needed
     Else
        Do this, or if you don't want to do anything, don't even use Else
     End If
   Next J
Next i
End If

[Thompyt]

June7,
Tried that and even included additional criteria

Dim rV As DAO.Recordset
Dim i As Integer, J As Integer, Units As String
Set rV = CurrentDb.OpenRecordset("Data")
      Do While Not rV.EOF
         rV.Edit
            
 If rV![Desc] Like "I # FIRE" And rV![Para] = "0109" Then
 For i = 1 To 3
    For J = 1 To 4
      K = Chr(J + 64)
            If rV![Bumper] = i & "RRR " & K & "/ZZZ" Then
                     Units = i & " " & K & " " & "ZZZ"
            ElseIf rV![Bumper # / Plat ID] = i & "PLT " & K & "/YYY" Then
                     Units = i & " " & K & " " & "YYY"
            ElseIf rV![Bumper # / Plat ID] = i & "PLT " & K & "/XXX" Then
                      Units = i & " " & K & " " & "XXX"
  End If
    Next J
  Next i
End If
                    rV![NUnits] = Units
                rV.Update
            rV.MoveNext
      Loop
End Sub

This will keep updating rV![Nunits] until EOF. thus asking about else do nothing

I think it has to do with
If rV![Bumper] = i & "RRR " & K & "/ZZZ" Then
Units = i & " " & K & " " & "ZZZ"

part of the code. maybe the / in the quotes? If the criteria is met then it will update to the correct output, but if it is not and is the last one of the data set, then it will repeat the last output until EOF.

I tried:

Else: Units = ""
                    rV![NUnits] = IIF(Units="", rV![Nunits], Units)
                rV.Update
            rV.MoveNext
      Loop

With this, I get no updates, indicating The If Code isn't identifying correctly.


Examples of rV![Bumper] to Units:
1RRR A/ZZZ 1 AZZZ
1RRR B/ZZZ 1 BZZZ
1RRR C/ZZZ 1 CZZZ
1RRR D/ZZZ 1 DZZZ
2RRR A/ZZZ 2 AZZZ
2RRR B/ZZZ 2 BZZZ
2RRR C/ZZZ 2 CZZZ
2RRR D/ZZZ 2 DZZZ
3RRR A/ZZZ 3 AZZZ
3RRR B/ZZZ 3 BZZZ
3RRR C/ZZZ 3 CZZZ
3RRR D/ZZZ 1 DZZZ........So on and so forth

1RRR A/YYY
1RRR B/YYY
1RRR C/YYY
1RRR D/YYY
2RRR A/YYY
2RRR B/YYY
2RRR C/YYY
2RRR D/YYY
3RRR A/YYY
3RRR B/YYY
3RRR C/YYY
3RRR D/YYY

1RRR A/XXX
1RRR B/XXX
1RRR C/XXX
1RRR D/XXX
2RRR A/XXX
2RRR B/XXX
2RRR C/XXX
2RRR D/XXX
3RRR A/XXX
3RRR B/XXX
3RRR C/XXX
3RRR D/XXX

[June7]

Provide a sample of Data.

[Thompyt]

Attached is a sample of the data: SampleData.zip

[June7]

Advise NOT to use spaces nor punctuation/special characters (underscore only exception) in naming convention.

Simpler code for the Clear button:

CurrentDb.Execute "UPDATE Data SET Base = Null"

One fix is to set Units to "" after updating the record. However, I never allow empty string in table. Consider:

Private Sub AddBase_Click()
Dim rV As DAO.Recordset
Dim i As Integer, J As Integer, K As String, Units As String, BN(3) As String
Set rV = CurrentDb.OpenRecordset("SELECT * FROM Data WHERE IsNumeric(Left([Bumper # / Plat ID],1))")
BN(1) = "ZZZ"
BN(2) = "YYY"
BN(3) = "XXX"
Do While Not rV.EOF
    rV.Edit
    If rV![Para Desc] Like "BN # Fire" And rV![Para #] = "0109" And Units = "" Then
        For i = 1 To 3
            For J = 1 To 4
                K = Chr(J + 64)
                If rV![Bumper # / Plat ID] = i & "PLT " & K & "/ZZZ" Then
                    Units = i & " " & K & " ZZZ"
                ElseIf rV![Bumper # / Plat ID] = i & "PLT " & K & "/YYY" Then
                    Units = i & " " & K & " YYY"
                ElseIf rV![Bumper # / Plat ID] = i & "PLT " & K & "/XXX" Then
                    Units = i & " " & K & " XXX"
                End If
             Next J
         Next i
    End If
    rV![Base] = Units
    rV.Update
    rV.MoveNext
    Units = ""
Loop
End Sub

Note the indentation. Your code indenting is awkward.

If all you want to do is remove PLT and / from the string, simpler ways to accomplish.

[Thompyt]

June7,
Thanks. I haven't had a chance to play with the code much today. Correct I am removing PLT and /, but the ZZZ/YYY/XXX are only sanitized unit sets. They actually look different with the units output. I could do Replace(rV![Bumper # / Plat ID],"PLT","") ..... true.

I was doing:
Else: Units = ""

Replace rV![Base] = Units with rV![Base] = IIF(Units="", rV![Base], Units). I will try it how you have it and see the outcome and update.

Thanks for the formatting tips, I know about the correct naming conventions. Not my data and I have to give back the updated data back in the same format.

I appreciate the help.


Edit - Update. It works with where you placed Units="", with Set rV = CurrentDb.OpenRecordset("Data") and with Set rV = CurrentDb.OpenRecordset("SELECT * FROM Data WHERE IsNumeric(Left([Bumper # / Plat ID],1))"). I think I see where you are going with this - extra insurance it will only look at those criteria where there field data starts with a number.

Thanks again.