Results 1 to 8 of 8
  1. #1
    Bernard is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Posts
    7

    In an access 2013 report I want to null one field if the other is not null

    In an Access 2013 Report with totals, I want to make field (a) null if field(b) is not null, but I cannot get my expression accepted in BUILD in the control field.
    the following is one of many expressions I have tried unsuccessfully.


    Code:
     IIf([pmnt2]Not Null=([payment 1])Is Null)
    Any help would be greatly appreciated.
    I am on Windows10 (64 bit) using ACCESS2013

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    In the control for field a, try = IIF(Is Null([fieldb],[field a], Null))
    I'm assuming the controls involved are not bound, else you probably will not be able to set it to Null (or anything else for that matter).
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Bernard is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Sep 2015
    Posts
    7
    Quote Originally Posted by Micron View Post
    In the control for field a, try = IIF(Is Null([fieldb],[field a], Null))
    I'm assuming the controls involved are not bound, else you probably will not be able to set it to Null (or anything else for that matter).
    Thanks for help, but the suggested code doesn't work. it failed with message #"You may have entered a comma without a preceding value or identifier"

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I may have misplaced a parenthesis.

    = IIF(Is Null([fieldb]),[field a], Null)

    If that's not correct either, post what you try.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Bernard is offline Novice
    Windows 10 Access 2013
    Join Date
    Sep 2015
    Posts
    7
    #= IIF(Is Not Null([pmnt2]),[payment 1], Null)#
    the above is a copy (copy & paste) of the code I used this time in payment 1 field (fieldb) control box. This also failed with the following message:
    "The expression you entered contains invalid syntax.
    You may have entered a comma without a preceding value or identifier"
    I have saved a screen print of the result, but don't know how to show you that. (anyway, probably no use because no different from what I have said)

  6. #6
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Umm, what's with the pound signs - #?
    Where did you get Is Not Null from? There is no such syntax AFAIK.
    Plus I think you have the logic backwards. Anyway, one thing at a time.
    Start with what I posted or try
    = IIF(Not IsNull([pmnt2]),[payment 1], Null) This will return payment 1 if there is anything in payment 2, which I thought to be the reverse of what you asked for.
    You might also notice I corrected my use of Is Null to IsNull. Sorry.
    Hang on while I test it, as an after thought to this post...
    EDIT: I guess I'm slipping as I get older...
    You can't manipulate a control like this if it has an expression as a control source. So try this. In the AfterUpdate event for the second control, put

    Me.payment1 = IIf(IsNull(Me.pmnt2), Me.payment1, Null)

    Obviously you have to use the correct names for the form controls. You shouldn't need to requery the control after this line, it should update automatically. As an alternative, you can assign the code line to a button click if need be. It works for me. I think also it applies the action you are after.
    Last edited by Micron; 11-06-2016 at 02:33 PM. Reason: correction

  7. #7
    Bernard is offline Novice
    Windows 10 Access 2013
    Join Date
    Sep 2015
    Posts
    7
    Thanks very much for your expression = IIF(Not isNull([pmnt2]),[payment 1], Null) This is exactly what I wanted.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Glad I was able to (finally) help.

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

Similar Threads

  1. Display zero instead of null? Access 2013
    By Lynn Cohen in forum Access
    Replies: 1
    Last Post: 07-20-2015, 09:50 AM
  2. Replies: 1
    Last Post: 03-12-2014, 03:13 AM
  3. Replies: 3
    Last Post: 11-13-2013, 08:56 AM
  4. Replies: 9
    Last Post: 12-05-2012, 02:20 PM
  5. If/Then Null based on Null Value of Another Field
    By vbpeterson in forum Queries
    Replies: 11
    Last Post: 06-05-2012, 05:00 PM

Tags for this Thread

Posting Permissions

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