IIF NULL not working

[krutoigoga]

Hello.
I've created a DAILY report to show data (text) entered into a table and I need help with it.
The text box in the report currently has the following formula:
=[P1.SKU] & " " & [P1.Notes] & Chr(13) & Chr(10) & [P2.SKU] & " " & [P2.Notes]
P1 and P2 aew queries (with criteria) of the original table. The criteria in the query is Date() under the dates field, which only shows entries from today.
I am trying to get the report text box to show only 2 fields from the P! and P2 queries: SKU and NOTES.


The problem I am running into is that when the query or table has no entries I get an ERROR in the report.
I tried =IIf(IsNull([P1.SKU] & " " & [P1.Notes])," ",[P2.SKU] & " " & [P2.Notes]) however it did not resolve the error.
The report also has various other text boxes that are pulling data (also text) from a form that is used to enter data into a different table.
Please help
thank you.

[ConneXionLost]

Just a guess, try:

=IIf(IsNull([P1.SKU]) AND IsNull([P1.Notes])," ",[P2.SKU] & " " & [P2.Notes])

Cheers,

[CGM3]

Actually, I think it should be:

=IIf(IsNull([P1.SKU]) OR IsNull([P1.Notes])," ",[P2.SKU] & " " & [P2.Notes])

With the AND, if one value is Null and the other isn't, it'll try to concatenate them, which will error out owing to the Null value.

[krutoigoga]

Hi guys.
thanks for the suggestions. I tried them and still can't get it to work. I have attached a test bd. I m trying to get the report to show any data that was entered today. Any ideas what else I can try?

[ConneXionLost]

In your database you have three tables. There are no relationships established between these tables.

Your report uses this embedded query as it's source:

SELECT Main_Query_1.field_a, Main_Query_1.field_b, Main_Query_1.field_c, P1.Notes, P1.SKU, P2.Notes, P2.SKU, Main_Query_1.dates
FROM Main_Query_1, P1, P2;

again, there are no relationships identified.

There is no logic in this model.

[krutoigoga]

I didn't think relationships would be necessary in this case. But I tried it anyway:

SELECT Main_Query_1.field_a, Main_Query_1.field_b, Main_Query_1.field_c, P1.Notes, P1.SKU, P2.Notes, P2.SKU, Main_Query_1.dates
FROM (Main_Query_1 INNER JOIN P1 ON Main_Query_1.dates = P1.Dates) INNER JOIN P2 ON P1.Dates = P2.Dates;


and still ..... nothing.

Any other suggestions??

p.s. weird thing is... if there is data in all three queries (i.e. entries from todays date), the report will show them all. If one of the queries does not bring back and data ... the report will not show any of them.

[Xiaoding]

I hope this gets answered, I am having the same difficulty. I have looked all ove the web, and it crops up now and then, but no one seems to know the answer!

I was thinking ot trying the iif command, looks like a dead end now, though I will try it in my case.

An idea: put the query in a macro, perhaps the macro will supress the error? It's worth a shot. I have tried having the query put the results into a table, that did not work either, but perhaps a macro would, and then run a code module to change any errors to zero, or whaterver you want.