Results 1 to 7 of 7

Conditionally Filtering for Either/or Record

  1. #1
    chasm58 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2016
    Posts
    4

    Conditionally Filtering for Either/or Record

    My report currently pulls records from 2 tables.
    There are 2 fields in one of the tables for where one (but not both) of the fields records will be blank.
    I need to have these values occupy the same physical space on the report.
    First, should I use a Query rather than the tables?
    I have tried to do this with an IsNull Criteria in one of the two fields...no luck.
    Suggestions, please.

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,591
    Yes, do it in a query. You could also have two textboxes on top of each other and make the one invisible, but this tends to get a bit cumbersome. For reports, always make the query first which shows the data exactly as you want it to show on the report.

  3. #3
    Micron is online now VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    3,031
    I need to have these values occupy the same physical space on the report.
    Does this mean that if one value is there and not the other you don't want data sometimes showing as if left justified and other times as right justified because data is in either the left or right control? Then you need to hide the 2 controls and add a calculated textbox on the report: =[txtLeft] & " " & [txtRight]
    The red part inserts space or whatever you'd need to separate the two values. If you left justify the text in this control, if the left value is Null, the right data will be left justified. If the right is Null, same. If both are there, same.

    You should still base a report (and most of the time, forms) on a query as suggested.
    - "doesn't work" is no help.Error #s/text do. State what's happening.
    - Use code tags for code/sql; show where errors occur
    Make all suggested changes in copies of your database or to its objects

  4. #4
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,163
    Yes your Report data should be a query. Create a new field in the query to do your condition on those 2 fields and display on your report:

    FieldData: IIF(isnull([Field1), [FIeld2], [FIeld1])

  5. #5
    chasm58 is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2016
    Posts
    4

    Clarification - Report Filtering

    Quote Originally Posted by Micron View Post
    I need to have these values occupy the same physical space on the report.
    Does this mean that if one value is there and not the other you don't want data sometimes showing as if left justified and other times as right justified because data is in either the left or right control? Then you need to hide the 2 controls and add a calculated textbox on the report: =[txtLeft] & " " & [txtRight]
    The red part inserts space or whatever you'd need to separate the two values. If you left justify the text in this control, if the left value is Null, the right data will be left justified. If the right is Null, same. If both are there, same.

    You should still base a report (and most of the time, forms) on a query as suggested.
    The data I am including in the report are actually barcodes based on 2 fields in the table(query), GTIN" and "UDI". For each record in my database, there will be either a GTIN barcode or a UDI barcode.
    I need this barcode to appear in the same area in the detail section of my report. The majority of records will have a GTIN barcode and not a UDI barcode so it makes sense to base the criteria around the exception, not the rule.
    I tried to use Not IsNull as criteria in the query for the UDI barcode and have the barcodes stacked upon each other.

    I would appreciate any suggestions. I think I am making this more difficult that it really is....

  6. #6
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,163
    Did you try my suggestion? It should put either value that is not null from either field into that new field, then use that new field on the report and it should display the value.

  7. #7
    Micron is online now VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    3,031
    Given your answer, I would agree with Bulzie's solution to add the calculated query field and bind your report control to it. However, that will not tell you which bar code it is, if that matters. To get that, you could either add the bar code type field to the query (but I'm betting you don't have a table for barcode types) or modify the suggestion like this:
    Code:
    BarCode: IIF([Field1] Is Null, "BarCodeType2: " & [Field2], "BarCodeType1: " & [Field1])
    as long as you don't mind having the type predicate before every value. You could also use a UNION query, but without a full understanding of the db, I don't see any advantage in that. You'd still have the issue of a generic field name for the bar code type.

    IsNull is a vba function. Is Null is for sql.
    Last edited by Micron; 01-10-2017 at 11:26 AM. Reason: code correction

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

Similar Threads

  1. Replies: 1
    Last Post: 05-31-2013, 10:02 AM
  2. Record filtering through Combo box
    By glen in forum Forms
    Replies: 5
    Last Post: 12-21-2012, 02:43 PM
  3. Myserious parameter during record filtering code
    By Monterey_Manzer in forum Forms
    Replies: 1
    Last Post: 11-30-2012, 10:34 AM
  4. Conditionally formatting a mm/dd/yyyy*
    By Steve3415 in forum Reports
    Replies: 4
    Last Post: 03-11-2012, 01:36 PM
  5. Replies: 3
    Last Post: 01-14-2010, 09:42 AM

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