Nested If statements and data validation...

**d2ther** · 09-12-2013, 04:39 PM

To make a long story short I have this form

with a list-box of tests on the left that can be clicked to change to change the Sub Form on the right to insert laboratory test data into. The data is being copy-pasted from Excel sheets into the database via the Sub Form at the bottom, which works just fine an has saved us a TON of time. However... after much frustration (and arguing) with the person that designed code to check the inputted data's integrity I have decided to come to the forums for help. Here is the currently running If statement in the database:

Code:

Private Sub Form_AfterInsert()

If [TestReading] = "PASS" Then
    If [TestReading] < [Min] Then
    [PASSorFAIL] = "FAIL"
    End If
    If [TestReading] > [Max] Then
    [PASSorFAIL] = "FAIL"
    End If
    If [TestReading] = Null Then
    [PASSorFAIL] = "PASS"
    End If
    If [TestReading] > [Min] And [TestReading] < [Max] Then
    [PASSorFAIL] = "PASS"
    End If
End If

If [TestReading] = "FAIL" Then
    If [TestReading] < [Min] Then
    [PASSorFAIL] = "FAIL"
    End If
    If [TestReading] > [Max] Then
    [PASSorFAIL] = "FAIL"
    End If
    If [TestReading] = Null Then
    [PASSorFAIL] = "PASS"
    End If
    If [TestReading] > [Min] And [TestReading] < [Max] Then
    [PASSorFAIL] = "PASS"
    End If
End If

End Sub

Private Sub Form_AfterUpdate()
DoCmd.RefreshRecord

If [TestReading] = "PASS" Then
    If [TestReading] < [Min] Then
    [PASSorFAIL] = "FAIL"
    End If
    If [TestReading] > [Max] Then
    [PASSorFAIL] = "FAIL"
    End If
    If [TestReading] = Null Then
    [PASSorFAIL] = "PASS"
    End If
    If [TestReading] > [Min] And [TestReading] < [Max] Then
    [PASSorFAIL] = "PASS"
    End If
End If

If [TestReading] = "FAIL" Then
    If [TestReading] < [Min] Then
    [PASSorFAIL] = "FAIL"
    End If
    If [TestReading] > [Max] Then
    [PASSorFAIL] = "FAIL"
    End If
    If [TestReading] = Null Then
    [PASSorFAIL] = "PASS"
    End If
    If [TestReading] > [Min] And [TestReading] < [Max] Then
    [PASSorFAIL] = "PASS"
    End If
End If

End Sub

Private Sub Form_Current()
DoCmd.RefreshRecord

End Sub

So as you can see this is a simple "nested If statement" that checks each column of data that is inserted from Excel into Access and it checks whether the "PASS" is really a Pass and the "FAIL" is really a Fail... The main issues with this is that whenever the Subform is changed, clicked, or when records are pasted into the database Access hangs for long periods of time... (it IS running a central server over a network). The person that wrote the code is claiming its a networking error and refuses to fix the problem... but I think its a programming error and that the "Private Sub Form_Current()
DoCmd.RefreshRecord" command needs to be removed and replaced with something else.

And also, in my opinion shouldn't the VBA function be changed to something like this?:

Code:

Private Sub TestReading_Change()

If [PASSorFAIL] = "PASS" Then
    If [TestReading] = Null Then
    [PASSorFAIL] = "PASS"
    End If
    If [TestReading] < [Min] Then
    [PASSorFAIL] = "FAIL"
    End If
    If [TestReading] <= [Max] Then
    [PASSorFAIL] = "PASS"
    End If
    If [TestReading] >= [Min] Then
    [PASSorFAIL] = "PASS"
    End If
    If [TestReading] > [Max] Then
    [PASSorFAIL] = "FAIL"
    End If
End If

If [PASSorFAIL] = "FAIL" Then
    If [TestReading] = Null Then
    [PASSorFAIL] = "PASS"
    End If
    If [TestReading] < [Min] Then
    [PASSorFAIL] = "FAIL"
    End If
    If [TestReading] <= [Max] Then
    [PASSorFAIL] = "PASS"
    End If
    If [TestReading] >= [Min] Then
    [PASSorFAIL] = "PASS"
    End If
    If [TestReading] > [Max] Then
    [PASSorFAIL] = "FAIL"
    End If
End If

End Sub

I'm not sure if either my co-worker of myself is correct (I'm thinking we're are both wrong) but some help and direction would be greatly appreciated any if there are any questions I will answer them and provide more code if needed. I feel like this code is causing some major errors and upsets in our lab and to finally get it sorted out would be awesome!

Thanks in advance.

**June7** · 09-12-2013, 04:57 PM

I've never use RefreshRecord.

The code returns the same result whether PASSorFAIL equals Pass or Fail. And what 'each column'?
Consider:

Code:

With Me
If .PASSorFAIL = "PASS" Or .PASSorFAIL = "FAIL" Then
    Select Case .TestReading
        Case Null
            .PASSorFAIL = "PASS"
        Case Is < .Min
            .PASSorFAIL = "FAIL"
        Case Is > .Max
            .PASSorFAIL = "FAIL"
        Case Else
            .PASSorFAIL = "PASS"
    End Select
End If
End With

**d2ther** · 09-12-2013, 08:03 PM

Thanks for such and quick and detailed reply.

I was thinking the RefreshRecord was causing the hanging but wasn't certain. I was thinking something like DoCmd.Requery would fix the problem?
There's a separate Excel file that has columns and fieldnames at the top and the data is in rows. Kind of like this:

LotNo BatchNo TestReading Date Non-Printable Notes PASSorFAIL LotRelease Failed Lots LIST
100000 1 2 7-Sep-10 PASS

The user special pastes the records in as a CSV from Excel, the problem seems to be that the Columns in the spreadsheets are already predefined as PASS so when the user pastes the records into Access the statement just assumes such...?

**June7** · 09-12-2013, 09:24 PM

Don't understand how this procedure is associated with copy/paste of records. What 'statement' - the VBA code? Code only 'assumes' what you tell it to assume. I don't know if Requery is pertinent because I don't understand the process.

**d2ther** · 09-13-2013, 07:36 AM

The general problem right now is that the Excel sheets are already pre-populated with PASS in the PASSorFAIL column of the CVS tables.

So when the user tries to Copy-paste the records from Excel into Access the VBA code should be checking the TestReading column for data integrity, making sure that the test result isn't greater than the Max column or less than the Min column. So it checks whether a "PASS" is really a PASS and a FAIL is really a FAIL, but for some reason it doesn't work because the Excell records are already a Pass...?

The VBA code is in the event procedure of the form for the TestReading column of the records as and AfterUpdate() method... I guess... I've only dabbled in VB Script so forgive my illiteracy of the language.

I don't think the Requery or even RefreshRecord is necessary seeing as how it's already used in another section of the database where is works just fine an I feel like its just slowing down the form all together.

**June7** · 09-13-2013, 10:34 AM

After records are inserted into table en masse by copy/paste or import with wizard, options to modify the PASSorFAIL value:

1. run an UPDATE sql action

2. open recordset in VBA and cycle through the recordset

**ssanfu** · 09-13-2013, 02:16 PM

PMFJI,

Looking at the code from your programmer, I see a couple of errors.

Code:

If [TestReading] = "PASS" Then
..
End If

If [TestReading] = "FAIL" Then
..
End If

It looks like [TestReading] (a numeric field??) is being compared to a text string.
This test will always equal FALSE in VBA. If used in a formula, you get a type mismatch error.

Code:

If [TestReading] = Null Then

This test will always be FALSE. NULL is not equal to anything, not even to NULL.

And also, in my opinion shouldn't the VBA function be changed to something like this?:

Touched on the NULL issue.

Code:

Private Sub TestReading_Change()

The change event is not the right event to use (IMO).

From HELP:
Setting the value of a control by using a macro or Visual Basic doesn't trigger this event for the control.
You must type the data directly into the control, or set the control's Text property.

I have never used the after insert event, but it probably is a better event to use than the TestReading_Change event.

June's code deals nicely with the NULL issue as well as being shorter...

My $0.02

**June7** · 09-13-2013, 02:43 PM

Actually, I should have done some testing. The Null case doesn't work and it isn't needed.

Code:

With Me
If .PASSorFAIL = "PASS" Or .PASSorFAIL = "FAIL" Then
    Select Case .TestReading
        Case Is < .Min
            .PASSorFAIL = "FAIL"
        Case Is > .Max
            .PASSorFAIL = "FAIL"
        Case Else
            .PASSorFAIL = "PASS"
    End Select
End If
End With

**ssanfu** · 09-13-2013, 04:40 PM

OK, how about ..

*** Notice that I changed the "Min" & "Max" field names in my examples.

"Min" & "Max" are reserved words and shouldn't be used as object names.
see http://allenbrowne.com/AppIssueBadWord.html#M

Code:

   With Me
      If .PASSorFAIL = "PASS" Or .PASSorFAIL = "FAIL" Then
         Select Case .TestReading
            Case Is < .MinReading, Is > .MaxReading
               .PASSorFAIL = "FAIL"
            Case Else
               .PASSorFAIL = "PASS"
         End Select
      End If
   End With

Or even:

Code:

   With Me
      If .PASSorFAIL = "PASS" Or .PASSorFAIL = "FAIL" Then
         .PASSorFAIL = "PASS"
         Select Case .TestReading
            Case Is < .MinReading, Is > .MaxReading
               .PASSorFAIL = "FAIL"
         End Select
      End If
   End With

**June7** · 09-13-2013, 06:04 PM

Or maybe as simple as

Code:

With Me
   If .PASSorFAIL & "" <> "" Then
      .PASSorFAIL = IIf(.TestReading < .MinReading Or .TestReading > .MaxReading, "FAIL", "PASS")
   End If
End With

**ssanfu** · 09-13-2013, 06:46 PM

What about if "PASSorFAIL" is NULL??

Consider:

Code:

With Me
   .PASSorFAIL = "PASS"    'set default
   If .PASSorFAIL & "" <> "" Then
      .PASSorFAIL = IIf(.TestReading < .MinReading Or .TestReading > .MaxReading, "FAIL", "PASS")
   End If
End With

**June7** · 09-13-2013, 10:00 PM

Going by the original code, the OP had no need to evaluate TestReading if PASSorFAIL is null and possibly the field won't have any nulls: in post 3 "... already predefined as PASS so ...".

So if the field already has "Pass" in every record, maybe just the one line:

Me.PASSorFAIL = IIf(Me.TestReading < Me.MinReading Or Me.TestReading > Me.MaxReading, "FAIL", "PASS")

Nested If statements and data validation...

Thread Tools

Nested If statements and data validation...

Similar Threads

applying nested if statements of X = X-1 type logic

Comparing Dates in nested IIF statements

Nested If Statements Using Or (Logic Error)

Nested Select Statements in FROM clause

Nested IIF statements?

Posting Permissions