Conditionally Filtering for Either/or Record

[chasm58]

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.

[aytee111]

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.

[Micron]

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.

[Bulzie]

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])

[chasm58]

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....

[Bulzie]

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.

[Micron]

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:

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.