If text box has no value then show.....

You keep saying that P1 and P2 are different queries. But your report's Record Source has both of them in it, correct? You can't just refer to queries if they are not part of the report's record source.

What is the SQL for your Report's Record Source?
 
SELECT Main_Query.field_a, Main_Query.field_b, Main_Query.field_c, Main_Query.dates, [P1 testing].DRV, [P2 testing].PASS
FROM (Main_Query LEFT JOIN [P1 testing] ON Main_Query.dates = [P1 testing].Dates) LEFT JOIN [P2 testing] ON Main_Query.dates = [P2 testing].Dates;
 
Okay, based on your query it should work with what you had listed with:

=IIf(IsNull([DRV]) And IsNull([PASS]),"No Issues",[DRV]+Chr(13) & Chr(10)+[PASS])

But two things. One, you need the correct spacing. If you don't it won't work. Also, make sure you don't have controls named the same as those two fields.

You should then be able to use

=IIf(IsNull([DRV]) And IsNull([PASS]),"No Issues", [DRV] & Chr(13) & Chr(10) & [PASS])
 
I checked for spaces, seems like access removes any extra spacing on its own.
I have also checked the controls (to the bst of my ability) and don't seem to be the same as the two fields.
I have attached another database. The issue seems to be with fields A, B, C and Dates. Where there is some data in there the null formula
Code:
=IIf(IsNull([P1 testing.DRV]) And IsNull([P2 testing.PASS]),"No Issues",[P1 testing.DRV]+Chr(13) & Chr(10)+[P2 testing.PASS])
displays nothing, but if those fields are NOT populated the code shows "No Issues"
Can you please check the attachment? I have no idea what else I can do.
Thanks.
 

Attachments

No resolution found yet. I temporarily added a text box which is filled in the form to say "No issues".
 
OK just looking at that bit of code in #24, chr(13) and chr(10) are CR and LF respectively yeah? Using + instead of & means that Access is trying to add P1 Testing.drv and CR together, and LF and p2 testing.pass together, not concatenating them as I suspect you need.
 
OK just looking at that bit of code in #24, chr(13) and chr(10) are CR and LF respectively yeah? Using + instead of & means that Access is trying to add P1 Testing.drv and CR together, and LF and p2 testing.pass together, not concatenating them as I suspect you need.

Nope not quite there James. The + instead of & will propagate NULLs which then means that they won't include the CR LF if the field added are null. It is a completely valid approach.
 
Well I did not know that! Every day's a school day, cheers Bob! So does that mean that if I used [Field1]+[Field2], and field1 was null and field2 wasn't, the expression would result in a null?

(Wasn't criticising BTW, you are some sort of Access genius after all!)
 

Users who are viewing this thread

Back
Top Bottom