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

[krutoigoga]

Hello good people.
I'm working with a report that pulls data (text) from a query and it works fine.

What I would like to do is:
>>> IF a text box (text71) in the report is blank (no data found in the query) then show "No Issues"

I currently have
=[P1.DRV] & Chr(13) & Chr(10) & [P2.PASS]
in a text box (text71) and it shows what the query finds (P1 and P2 are queries)

What I have tried:
=IIf([P1.DRV] & Chr(13) & Chr(10) & [P2.PASS],Null, "No Issues")
=IIf([Text71] Is Null;"No Issues")
=IIf([Text71],Null, "No Issues")
In all cases it just comes up as blank/empty and does not show "No issues"

Please help.

 

[rainman89]

Why not do that In the query itself rather then messing with it in the report? Might work better for you!

 

[krutoigoga]

Why not do that In the query itself rather then messing with it in the report? Might work better for you!

how would I do that in a query? Just type the formulas in the criteria?

 

[rainman89]

Well I think you would want something like

Issues: IIf(IsNull([DRV]) And IsNull([PASS]),"No Issues",[DRV] &  [PASS])

 

[krutoigoga]

Well I think you would want something like

Issues: IIf(IsNull([DRV]) And IsNull([PASS]),"No Issues",[DRV] &  [PASS])

thanks - I tried

=

IIf(IsNull([P1.DRV] & Chr(13) & Chr(10) & [P2.PASS]),"No Issues",[P1.DRV] & Chr(13) & Chr(10) & [P2.PASS])

but im still getting a blank/empty - it doesnt show "no issues"

P.s. P1 and P2 are the queries, DRV and PASS is a concatenate of two fields in those queries. So DRV and PASS and fields from 2 difefrent queries.

 

[rainman89]

can you post up a sample of your database?

I dont think

[P1.DRV] & Chr(13) & Chr(10) & [P2.PASS]

can ever be null since you are putting the Chr(13) & Chr(10) in there

 

[krutoigoga]

Attached a test database.
Im using Chr(13) & Chr(10) for the new line. Dunno if that causes issues with null
NOTE: Once you open the attachment, you will find the report shows data in the text box, but if you change the date in table P1 to yesterday (or something other than today) you will find the text box to be blank/empty.
Thanks

 

[boblarson]

Attached a test database.
Im using Chr(13) & Chr(10) for the new line. Dunno if that causes issues with null

Yes, it would. As noted if those are there then it is NOT Null. You can use + instead of & on either side to propagate nulls.

[Field1] + Chr(13) & Chr(10) + [Field2]

Then if both Field1 and Field2 are null, the whole thing will be null.

 

[krutoigoga]

Yes, it would. As noted if those are there then it is NOT Null. You can use + instead of & on either side to propagate nulls.

[Field1] + Chr(13) & Chr(10) + [Field2]

Then if both Field1 and Field2 are null, the whole thing will be null.

Tried your idea as well and unfortunately its still blank

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

 

[boblarson]

Change this part:

=IIf(IsNull([P1.DRV]+Chr(13) & Chr(10)+[P2.PASS]),"

to this:

=IIf(IsNull([P1].[DRV]) And IsNull([P2].[PASS]),"

 

[rainman89]

Change this part:

=IIf(IsNull([P1.DRV]+Chr(13) & Chr(10)+[P2.PASS]),"

to this:

=IIf(IsNull([P1].[DRV]) And IsNull([P2].[PASS]),"

see above!

 

[boblarson]

see above!

Yep, you called it but the OP seems bent on putting Chr(13) & Chr(10) in the null test which SHOULD NOT BE.

 

[krutoigoga]

Guys - Kudos!
thank you for your help. I now understand why Chr(13) & Chr(10) was not required in the first part (with Null)
I tried

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

and the issue is resolved.
Thanks again - all of you.

 

[JamesMcS]

If you're just trying to find out if a report contains no data, and display a message if that's the case, why not just use dcount in the report's on open?

if dcount("*",[source name])=0 then
msgbox "No records"
docmd.close
Else
Endif

 

[krutoigoga]

Im just trying a few things and renamed the queries P1 and P2 to "P1 testing" and "P2 testing" resp.
Of course adjusted the formula and updated the record source in the report accordingly.

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

Well now it is not working. Any ideas?

 

[boblarson]

You are putting the square brackets wrong. You need to use

[P1 testing].[DRV])

not all together in them.

 

[krutoigoga]

You are putting the square brackets wrong. You need to use

[P1 testing].[DRV])

not all together in them.

Hi boblarson. I tried that from your earlier post - however access give me a "Enter Parameter Vaue" for [P1 testing]

 

[boblarson]

Then that means you don't have that table included in the query.

 

[boblarson]

Also, if you don't have the same field names from each query you can simply shorten it to use the field names: [DRV] and [Pass]

 

[krutoigoga]

I removed all the queries from the report query and readded them all, set up the relationships and added appropriate fields.

In the text box with

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

...... i get a blank

in the text box with

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

....... i get an #Error

Is it because P1 and P2 are 2 different queries?
I don't know why the null stopped working, I had it in the morning