Else criteria not working (1 Viewer)

[VincentSp]

Hi All,

I made a simple If criteria, but for some odd reason the Else part of the criteria is not working.
I am probably overlooking something simple, but I cannot seem to find it.
Hopefully some can help.

If Me.Text18.Value >= Me.Text28 Then
Me.Text20.Value = Me.Text18
Else
Me.Text20.Value = Me.Text28

End If

Regards,

Vincent

 

[cheekybuddha]

Hi Vincent,

What sort of values are you comparing? Numeric?

Can you give some examples of the values that fail?

 

[VincentSp]

Hi,

There all Numeric values.

If the value of textbox18 is greater then 90,120 or 180 depending on the current value of textbox28.
Then the value of textbox18 should be used for textbox20 else textbox28 should be used for textbox20.

Regardless of the value of textbox 18, If I use > it will only return the value of textbox28, and if I use < it will only return the value of textbox18.

 

[cheekybuddha]

Which event is your code called from?

 

[VincentSp]

After update of the field which is used for the calculated value of textbox18

 

[cheekybuddha]

What is the ControlSource of Text18?

If it is a calculated textbox, are you sure its AfterUpdate event actually fires?

 

[arnelgp]

should be on AfterUpdate event of both Text18 and Text28:

Private Sub Text18_AfterUpdate()
    Call checkValue()
End Sub

Private Sub Text28_AfterUpdate()
    Call checkValue()
End Sub

Private Sub checkValue()
    If Nz([Text18], 0) >= Nz([Text28], 0) Then
        [Text20] = [Text18]
    Else
        [Text20] = [Text28]
    End If
End Sub

 

[VincentSp]

unfortunately still not working..

Textbox18 is a calculated textbox; =([numberofobjects]*[text12])+[text14]
Textbox28 is a textbox based on a combo box; =[Object].[Column](4)
Textbox20 is a unbound textbox

The after update event is on field numberofobjects

Textbox12 and 14 used in the calculation are both a textbox based on a combo box; =[DiameterObject].[Column](3) and =[Description].[Column](3)

Hope this helps..

 

[zeroaccess]

Add a Me.Requery at the end

Or Requery only the controls that are changing if you have screen flicker issues.

 

[cheekybuddha]

Hi Vincent,

I would advise you give your controls more meaningful names!!

Do you have a combobox called 'Object'?

Try:

Private Sub numberofobjects_AfterUpdate() 

  Dim vTest As Variant
  Dim vCompare As Variant

  With Me
    vTest = .numberofobjects * .DiameterObject.Column(3) * .Description.Column(3)
    vCompare = .[Object].Column(4)
    Debug.Print "vTest:", Nz(vTest, "NULL")
    Debug.Print "vCompare:", Nz(vCompare, "NULL")
    If vTest >= vCompare Then
      .Text20 = vTest
    Else
      .Text20 = vCompare
    End If
  End With

End Sub

 

[VincentSp]

No the field is called 'DiameterObject'

Your code does the same and somehow only returns the value of [DiameterObject].Column(4) to textbox 20.
Regardless of the value of numberofobjects

 

[cheekybuddha]

Change:
vCompare = .[Object].Column(4)
to
vCompare = .DiameterObject.Column(4)

 

[VincentSp]

Still the same results.. only returns the value from textbox28 regardless of the Textbox18 value..

 

[cheekybuddha]

What is printed in the Immediate Window (Ctrl+G) ?

 

[Darrell]

arnelgp's solution should work if your textboxes are formatted in a number format, what are they currently..?

 

[zeroaccess]

Still the same results.. only returns the value from textbox28 regardless of the Textbox18 value..

See my post above about the requery. Is this on a form? If this is on a form, you have to requery the controls for the changes to display.

 

[VincentSp]

Finally got it working.. I made all the fields with calculations bound fields (named them in my table 'calculatingfield1' 'calculatingfield2' etc.) instead of unbound textboxes and this did the trick.
First of all thank you for all your help and suggestions!!
But because I'm here to learn got someone explain why this only works with bound fields?

 

[The_Doc_Man]

First, I'll make a suggestion to keep you from going crazy with unneeded typing. When you have Me.Textnn.Value, the .Value is redundant. If the named control even HAS a value then .Value is the default property and does not have to be specified. I said it that way because there are controls that have no values. Labels, lines, and rectangles, for example, have no value so have no .Value either.

Regarding this statement:

Regardless of the value of textbox 18, If I use > it will only return the value of textbox28, and if I use < it will only return the value of textbox18.

That sounds more like the behavior of text comparisons. I.e. those numbers aren't acting like numbers. They are acting like digit strings. When comparing numbers, 90 < 180, but when comparing strings 90 > 180.

Having bound controls means the format of the value is the format of the underlying field. For an unbound control, even though the control might be in digit form, this is actually an "ambiguous" value depending on how you were going to use it.