If statement won't work (1 Viewer)

[dkmoreland]

For the life of me, I cannot tell why the if statement below (highlighted in red) will not work. I have been running it through the debugger all day but I just am not seeing it.

I am trying to add records from a spreadsheet to a table. I need to exclude records with a sales class value of 25 or 26. The code works - it creates the records - but does not exclude those defined in the if statement. It grabs everything.

I have said many, many bad words trying to make this work. Suggestions, please?

Private Sub CmdPrepKPI_Click()

Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Dim FirstSeparator As Integer
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rsDefaults As DAO.Recordset
Dim strMsg As String
Dim strSQL As String
Dim strJob As String
Dim strImportFile As String
Dim strJobName As String
Dim dStart_date As Date
Dim dEnd_date As Date
Dim strDateText As String
Dim strSalesClass As String



On Error GoTo ErrProc
    Set db = CurrentDb()

    strImportFile = Me.TextImport
        
    If Me.TextImport = "" Then
        Exit Sub
        MsgBox "Please select a file.", vbOKOnly
    End If
    
    
    Set rs = db.OpenRecordset("Production Raw Data")
    
    
    'open spreadsheet (Input Sheet) page, get data from specific cells
    Set appExcel = Excel.Application
    Set wbk = appExcel.Workbooks.Open(Me.TextImport)
    
    'populate the date fields
       
    strDateText = Range("A7").Value
    
    i = InStr(strDateText, "To:")
    
    dStart_date = Trim(Mid(strDateText, 6, i - 6 - 1))
    
    dEnd_date = Trim(Mid(strDateText, i + 3))
    
    
    
    'populate the job number and number
    intRow = 11
    Debug.Print intRow
    strSalesClass = Right(Range("J" & intRow).Value, 2)
    Debug.Print strSalesClass
            
    Do While Len(Range("A" & intRow).Formula) > 0
    ' repeat until first empty cell in column A
    With rs
    
        If Not IsEmpty(Range("B" & intRow)) Then

            [COLOR="Red"][B]If strSalesClass <> "25" Or strSalesClass <>"26" Then
                .AddNew ' create a new record
        
                ' add values to each field in the record
                .Fields("JobNum") = Range("B" & intRow).Value
                .Fields("JobName") = Range("E" & intRow).Value
                .Fields("sales_class") = Range("J" & intRow).Value
                .Fields("start_date") = Format(dStart_date, "Short Date")
                .Fields("end_date") = Format(dEnd_date, "Short Date")
                
            End If[/B][/COLOR]
            
            .Update ' stores the new record
            
        End If
            
    End With
        intRow = intRow + 1 ' next row
        
Loop
    
    
    rs.Close
     
      
    'close excel
    wbk.Close (False)   'close without saving changes
    appExcel.Quit
    Set wbk = Nothing
    Set appExcel = Nothing
    
    
    MsgBox "File " + strImportFile + " has been imported", vbOKOnly
    
    DoCmd.Close
        
    
ExitProc:
    Set db = Nothing
    Exit Sub
ErrProc:
    Select Case Err.Number
        Case 462    'excel not open
            Set appExcel = New Excel.Application
            Resume Next
        Case 3125
            MsgBox "The selected workbook is not the correct format.", vbOKOnly
            Resume ExitProc
        Case 3201
            MsgBox "Job Prefix is not valid.  Please add the new prefix to the prefix list.  Import was cancelled.", vbOKOnly
            Resume ExitProc
        Case Else
            MsgBox Err.Number & "--" & Err.Description
            Resume ExitProc
            Resume Next
    End Select

End Sub

 

[isladogs]

Try And instead of Or

 

[dkmoreland]

Try And instead of Or

Sorry, but that did not work. It still picked up the ones I'm trying to exclude. This is so weird - the if statement is not working at all, whether I set it up to get the ones that equal 25 or 26 or try to exclude them. It's like it's not even there.

 

[isladogs]

Are these numbers or text values?
In other words should the quotes be there?

If that's not the solution try stepping through the code to see whether it is being bypassed

 

[dkmoreland]

Are these numbers or text values?
In other words should the quotes be there?

If that's not the solution try stepping through the code to see whether it is being bypassed

The numbers are text values. I have stepped through the code many times. It is not being bypassed - it just acts like the exclusion parameters are not even there.

 

[isladogs]

Think I may have spotted the issue.
Move the .Update above End If

 

[dkmoreland]

Think I may have spotted the issue.
Move the .Update above End If

sorry - that didn't make any difference. I'll look at it again tomorrow.

 

[isladogs]

Ok. I'm out of ideas anyway. Perhaps post your db tomorrow

 

[MarkK]

You can't test for inequality separately on two different values, because one or the other of them will always be unequal. You need to test for equality, and then negate that result, like...

If Not (strTest = "25" or strTest = "26") Then

You could also use Select Case and ignore this case, like...

Select Case strTest
   Case "25", "26"
[COLOR="Green"]      'ignore these[/COLOR]
   Case Else
[COLOR="green"]      'handle everything else here[/COLOR]
End Select

hth
Mark

 

[isladogs]

Doh! That's what my first attempt was trying (and failing) to do. Obvious now!

 

[MarkK]

I know, boolean logic can get really sneaky.
Mark

 

[arnelgp]

Or you may try:

Trim(strSalesClass)<>"25" or ...

Or:

If Instr("/25/26/", "/" & Trim(strSalesClass) & "/")=0 then

 

[dkmoreland]

You can't test for inequality separately on two different values, because one or the other of them will always be unequal. You need to test for equality, and then negate that result, like...

If Not (strTest = "25" or strTest = "26") Then

You could also use Select Case and ignore this case, like...

Select Case strTest
   Case "25", "26"
[COLOR="Green"]      'ignore these[/COLOR]
   Case Else
[COLOR="green"]      'handle everything else here[/COLOR]
End Select

hth
Mark

Select Case did the trick! I tried If Not but it didn't work either, as long as I was testing inequality for 2 values. When I just used one, it worked fine. So I decided to use Select Case instead of nested If statements.

It never occurred to me that testing inequality for 2 values was causing my problem. I really appreciate the help. I'm marking this one solved!