Unconventional Conditional VBA Formating

[mdjks]

I have created a form to cross refrence part numbers among suppliers. When the manager selects the part they normally order it will show below 7 possible alternate suppliers of an alternate part. I need the text of the lowest price supplier's part to be red.

The subform containing the pricing is based on a flat file and works the way I want it to. I put in a conditional if statement (one here is abreviated)

If Me.MC.Value < Me.HL.Value _
And Me.MC.Value < Me.FS.value Then
Me.mc.ForeColor = vbRed
Else
Me.mc.ForeColor = vbGreen
End If

This works but not when one of the values is null (some always will be). I have tried filling with a fake high number and having it's value white but am getting green where I want red and black for the high number.

I'm using the green just to tell me that the code is doing something, I'll have it black when finished.

Any help would be appreciated, I know this is very unconventional but it is the way the data is. I've already stumped 2 Access programmers that are way better than me.

I'm using Access 2000 but will also need to make a 97 version.

 

[modest]

have you tried using the Nz() function?

 

[mdjks]

I have tried nz() the result was always green

 

[Mile-O]

You will need to further expand your logic to consider the facts that there may be Null values. You should use the IsNull() function to determine this.

 

[mdjks]

Thank you for your help. Below is the code I ended up with. I had to fill null values with 999999999 but didn't want anyone to see the number.

Private Sub Form_Current()

If Me.Mc.Value < Me.ID.Value _
And Me.Mc.Value < Me.pp.Value _
And Me.Mc.Value < Me.ma.Value _
And Me.Mc.Value < Me.hl.Value _
And Me.Mc.Value < Me.fs.Value Then
Me.Mc.ForeColor = vbRed
Else
If Me.Mc.Value = 999999999 Then
Me.Mc.ForeColor = vbWhite
Else
Me.Mc.ForeColor = vbBlack
End If
End If

If Me.fs.Value < Me.ID.Value _
And Me.fs.Value < Me.pp.Value _
And Me.fs.Value < Me.ma.Value _
And Me.fs.Value < Me.hl.Value _
And Me.fs.Value < Me.Mc.Value Then
Me.fs.ForeColor = vbRed
Else
If Me.fs.Value = 999999999 Then
Me.fs.ForeColor = vbWhite
Else
Me.fs.ForeColor = vbBlack
End If
End If

If Me.ID.Value < Me.fs.Value _
And Me.ID.Value < Me.pp.Value _
And Me.ID.Value < Me.ma.Value _
And Me.ID.Value < Me.hl.Value _
And Me.ID.Value < Me.Mc.Value Then
Me.ID.ForeColor = vbRed
Else
If Me.ID.Value = 999999999 Then
Me.ID.ForeColor = vbWhite
Else
Me.ID.ForeColor = vbBlack
End If
End If

If Me.ma.Value < Me.fs.Value _
And Me.ma.Value < Me.pp.Value _
And Me.ma.Value < Me.ID.Value _
And Me.ma.Value < Me.hl.Value _
And Me.ma.Value < Me.Mc.Value Then
Me.ma.ForeColor = vbRed
Else
If Me.ma.Value = 999999999 Then
Me.ma.ForeColor = vbWhite
Else
Me.ma.ForeColor = vbBlack
End If
End If

If Me.hl.Value < Me.fs.Value _
And Me.hl.Value < Me.pp.Value _
And Me.hl.Value < Me.ID.Value _
And Me.hl.Value < Me.ma.Value _
And Me.hl.Value < Me.Mc.Value Then
Me.hl.ForeColor = vbRed
Else
If Me.hl.Value = 999999999 Then
Me.hl.ForeColor = vbWhite
Else
Me.hl.ForeColor = vbBlack
End If
End If

If Me.pp.Value < Me.fs.Value _
And Me.pp.Value < Me.hl.Value _
And Me.pp.Value < Me.ID.Value _
And Me.pp.Value < Me.ma.Value _
And Me.pp.Value < Me.Mc.Value Then
Me.pp.ForeColor = vbRed
Else
If Me.pp.Value = 999999999 Then
Me.pp.ForeColor = vbWhite
Else
Me.pp.ForeColor = vbBlack
End If
End If
End Sub

 

[modest]

when comparing, i would replace null value with 0... but i'd like to see a picture or a sample database to look at. That just looks like tooo much code.



I'd probably load all your values that aren't into an array. Then loop through the array and look for the lowest and largest number.



This is an example to find the min/max:

Public Function NewFunction()
    Dim tmpArray
    Dim arrayVal
    Dim lVal, hVal
    
    tmpArray = Array(33, 34, 345, 23, 5, 2, 3454)
    
    arrayVal = 0
    For Each arrayVal In tmpArray
        If IsEmpty(lVal) Then lVal = arrayVal: hVal = arrayVal
        If arrayVal < lVal Then lVal = arrayVal
        If arrayVal > hVal Then hVal = arrayVal
    Next
End Function

 

[modest]

You will need to further expand your logic to consider the facts that there may be Null values. You should use the IsNull() function to determine this.

IsNull() is good for initialized variables, but in cases where the variable is never initialize, it may be empty... so include IsEmpty()

 

[mdjks]

Thank you all for you help. I did try the array method but it didn't work, or I didn't write it correctly. The way I did do it is working smoothly and doesn't add to processing time.

 

[modest]

you do know that you can set the conditional format property? so why, may i ask, are you resorting to these "unconventional" means

 

[mdjks]

The conditional format property was too limited. I needed to evaluate 6 text boxes for the lowest value. the built in conditional format only allows three conditions.