none
Logic doesn't work in VBA RRS feed

  • Question

  • I have some logic that is not making sense in the following code

     If Forms![navMenu].[mnuFinances] > [DS] > [sfrmTransEntry]![curDr] <> Null Then
            TransDr = Forms![navMenu].[mnuFinances] > [DS] > [sfrmTransEntry]![curDr]
        Else
            TransDr = 0
        End If

    When running the code, I can hover over the field [curDr] and see that it has a value.

    The code will then jump to the Else part of the statement instead of processing the first statement. I have tried adding in a test for a number value as well but this made no difference.

    Can anyone suggest a solution thanks

    Johnr8811

    Wednesday, January 30, 2019 12:46 PM

Answers

  • Thanks for your responses.

    There were 2 issues I had.

    Firstly the path to the control was incorrect. I had picked that up from another article I read which was allowing me to see the value in the control but not getting it back to the variable.

    I have a navigation menu set up by following a YouTube article "Access 2013 11 - Navigation Forms" at https://www.youtube.com/watch?v=Wphp1VJP7XE, this uses a subform container called DS.

    The code was being called from subform1 and not the main form. This meant I had to set the path to the control from where the code was being called and not from the top level.

    Once I got my head around the path issue I could then focus on the logic issue.

    If the control had a value, then it was not Null and would go to the Else part of the statement therefore I had to add the test for a value as well. (Note: I don't allow negative values as that is taken care of by being a Credit or a Debit in the transaction).

    I have since changed the code as follows and it now works OK.

    If Me.sfrmTransEntry.Form![curDr] <> Null Or Me.sfrmTransEntry.Form![curDr] > 0 Then
            TransDr = Me.sfrmTransEntry.Form![curDr]
        Else
            TransDr = 0
        End If

    Thanks again for the responses - I am self taught in Access VBA and sometimes the simplest things trip me up.

    • Marked as answer by johnr8811 Thursday, January 31, 2019 11:59 PM
    Thursday, January 31, 2019 11:59 PM

All replies

  • The expression Forms![navMenu].[mnuFinances] > [DS] > [sfrmTransEntry]![curDr] doesn't make sense to me. Is that really what you have?

    Try

    TransDr = Nz(…, 0)

    where … is your expression.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, January 30, 2019 12:54 PM
  • johnr8811,

    I absolutely have no idea why it would work with that formula.

    "Forms![navMenu].[mnuFinances]" might work

    "[DS]" might not work as it is referring to something on the Form (to what it was referring to) or something unknown.

    "[sfrmTransEntry]![curDr]" might work.

    Perhaps you should be looking into something like

    Eqv, Mod or Imp Operators.....sample

    Imp Operator Example This example uses the Imp operator to perform logical implication on two expressions. Dim A, B, C, D, MyCheck A = 10: B = 8: C = 6: D = Null ' Initialize variables. MyCheck = A > B Imp B > C ' Returns True. MyCheck = A > B Imp C > B ' Returns False. MyCheck = B > A Imp C > B ' Returns True. MyCheck = B > A Imp C > D ' Returns True. MyCheck = C > D Imp B > A ' Returns Null. MyCheck = B Imp A ' Returns -1 (bitwise comparison).


    Hope it helps.
    Thursday, January 31, 2019 3:37 AM
  • Thanks for your responses.

    There were 2 issues I had.

    Firstly the path to the control was incorrect. I had picked that up from another article I read which was allowing me to see the value in the control but not getting it back to the variable.

    I have a navigation menu set up by following a YouTube article "Access 2013 11 - Navigation Forms" at https://www.youtube.com/watch?v=Wphp1VJP7XE, this uses a subform container called DS.

    The code was being called from subform1 and not the main form. This meant I had to set the path to the control from where the code was being called and not from the top level.

    Once I got my head around the path issue I could then focus on the logic issue.

    If the control had a value, then it was not Null and would go to the Else part of the statement therefore I had to add the test for a value as well. (Note: I don't allow negative values as that is taken care of by being a Credit or a Debit in the transaction).

    I have since changed the code as follows and it now works OK.

    If Me.sfrmTransEntry.Form![curDr] <> Null Or Me.sfrmTransEntry.Form![curDr] > 0 Then
            TransDr = Me.sfrmTransEntry.Form![curDr]
        Else
            TransDr = 0
        End If

    Thanks again for the responses - I am self taught in Access VBA and sometimes the simplest things trip me up.

    • Marked as answer by johnr8811 Thursday, January 31, 2019 11:59 PM
    Thursday, January 31, 2019 11:59 PM