Results 1 to 7 of 7
  1. #1
    Njliven is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    86

    Show Not Null Fields in one Field

    Below is a statement I have in a query. Basically I want to bring back the text of FP, NTE or EST if the respective fields are not null. The query runs but I get a -1 in the field.
    Hope some one can help if this is possible. Thanks!

    Type: ([Fixed Price] Is Not Null) & " FP " Or ([Not to Exceed] Is Not Null) & " NTE " Or ([Estimated Time Expenses] Is Not Null) & " EST "

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Try:

    IIf(Not IsNull([Fixed Price]),"FP ", "") & IIf(Not IsNull([Not to Exceed]), "NTE ", "") & IIf(Not IsNull([Estimated Time Expenses]), "EST ", "")
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Njliven is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    86
    Works Sort of.... Sometimes it brings back the desired result, but mostly it brings back all three FP NTE & EST in the field. I am bringing back the actual fields separately to see what is happening. There are no spaces in the fields, they are really null as far as I can tell. There is only a value in one of the three fields, so why is it sometimes bringing back all three? Thanks!

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Are these text or Yes/No fields? If each record can have only one of these values then should be one field with 3 value choices.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Njliven is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    86
    No, these are not yes no fields. They are text fields. They are either blank or have an X in them.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    If the alias text displays then the field is NOT null, something is in there, possibly an empty string, which appears to be nothing but isn't. Access defaults to allow empty strings when a field is created. I don't allow empty strings so I always change that property when creating new text field. The following will handle if field is Null or empty string.

    IIf([Fixed Price] & ""<>"", "FP", "") & IIf([Not to Exceed] & ""<>"", "NTE", "") & IIf([Estimated Time Expenses] & ""<>"", "EST", "")

    Why did you have spaces on each side of the aliases if you expect only one value to return?

    Again, if each record should have value in only one of the 3 fields then should be only 1 field with 3 value choices (FP, NTE, EST) and Null or empty string would not be an issue and no need for this expression.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Njliven is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jul 2012
    Posts
    86
    That worked Thanks!

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

Similar Threads

  1. Show when object field is null
    By Accesfreak in forum Queries
    Replies: 3
    Last Post: 10-26-2012, 08:49 AM
  2. Query Help - Show field if another field is null
    By lukekonrad in forum Access
    Replies: 1
    Last Post: 03-05-2012, 04:02 PM
  3. Replies: 1
    Last Post: 07-30-2011, 03:21 PM
  4. Dont show null fields in report
    By senna in forum Reports
    Replies: 4
    Last Post: 03-03-2011, 02:30 PM
  5. Replies: 2
    Last Post: 01-03-2011, 05:17 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
  •  
Other Forums: Microsoft Office Forums