Results 1 to 6 of 6

"And" stops functions from working

  1. #1
    JimmD43 is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Mar 2011
    Posts
    12

    "And" stops functions from working


    I am trying to get this function to work, but the "And" combining the two parts of the "If" statement does not seem to be working. The two Ifs, Isnull and Me.Activecontrol = work separately, but when combined by the "And", it only seems to check the first one. So as it is written below, IsNull is checked, but me.ActiveControl = is not. If I swap the order, then me.ActiveControl = is checked, but IsNull isn't. Does anyone know how to get this to work? Thanks.

    If IsNull(Me.Test1) = True And me.ActiveControl = Me.Test1 _
    Then
    Me.TestResult1.value = ""

    ElseIf me.ActiveControl = Me.Test1 And IsNull(me.Test1) = False _
    Then
    Me.TestResult1.value = me.ActiveControl * 1.2

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,598
    The test doesn't make sense. If Test1 is Null, how could the active control be equal to it (nothing is = Null)? In words, what do you want the test to accomplish?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    JimmD43 is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Mar 2011
    Posts
    12
    Quote Originally Posted by pbaldy View Post
    The test doesn't make sense. If Test1 is Null, how could the active control be equal to it (nothing is = Null)? In words, what do you want the test to accomplish?

    I'm sorry, I'm pretty new at this. Here's what I want: on BeforeUpdate, if the active control is a certain control, in this case Test1, AND the value of the control is non-existent (e.g., a value was deleted from the control and now it has no value entered -- what I was trying to accomplish with IsNull), then the control TestResult1 will have no value. However, if the active control is Test1 and it has a value entered, then TestResult1 will be equal to Test1 * 1.2. I tried using Len() = 0 and others in place of null as well. Again, they work when I have two separate If commands, but not when I combine them into one with And.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,598
    I'm not sure I would use before update for this (are you using the form's or a control's?), but I don't know the overall situation. I suspect this is what you want:

    If Me.ActiveControl.Name = "test1" AND Len(Me.Test1 & vbNullString) = 0 Then

    From the sound of it, I'd more likely just have code in the after update event of Test1 to set the value of the other control.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    JimmD43 is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Mar 2011
    Posts
    12
    Quote Originally Posted by pbaldy View Post
    I'm not sure I would use before update for this (are you using the form's or a control's?), but I don't know the overall situation. I suspect this is what you want:

    If Me.ActiveControl.Name = "test1" AND Len(Me.Test1 & vbNullString) = 0 Then

    From the sound of it, I'd more likely just have code in the after update event of Test1 to set the value of the other control.
    Thanks for the reply. I'm having the same problem with your code sample and with AfterUpdate that I am with my sample and BeforeUpdate. It still isn't recognizing the second condition, after the AND. So TestResult1 will be updated to equal Test1 * 1.2, but if there is a value in Test1 and it is deleted, then TestResult2 retains the value it had previously, instead of also being updated with no value. That's the only problem here, I can't get TestResult1 to update with no value. If there's a way to get it to recognize the "" and apply it to TestResult1 then I don't need the And to work.

    I'm planning to make a function out of this and apply it to MANY different subs, so that's why I'm trying to do it like this.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,598
    Can you post the db? The test I posted should look for both Null and a zero length string.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. "Group By" causes "ODBC--Call Failed" error
    By kaledev in forum Queries
    Replies: 1
    Last Post: 03-09-2011, 02:43 PM
  2. Replies: 3
    Last Post: 04-10-2010, 10:22 AM
  3. Report "WHERE" clause not working
    By rsearing in forum Reports
    Replies: 4
    Last Post: 05-26-2009, 02:07 PM
  4. aSTR = Dir("C:\*.*") >> "Type Mismatch"
    By JGrant in forum Programming
    Replies: 1
    Last Post: 03-28-2009, 05:17 AM
  5. Replies: 1
    Last Post: 12-11-2008, 01:28 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums