IIF null in a report

[krutoigoga]

Hello.
I've created a DAILY report to show data (text) entered into a table and I need help with it. I tried searching the forum and could not find any answers.
I want the text box in the report to get data from 3 queries which have a date() criteria.
P1 and P2 are queries of the original table.
I tried formulas like:
=[P1.SKU] & " " & [P1.Notes] & Chr(13) & Chr(10) & [P2.SKU] & " " & [P2.Notes]
=IIf(IsNull([P1.SKU] & " " & [P1.Notes])," ",[P2.SKU] & " " & [P2.Notes])
=IIf(IsNull([P1.SKU]) OR IsNull([P1.Notes])," ",[P2.SKU] & " " & [P2.Notes])
The report shows all data ONLY if all 3 tables have some data (text) entered.
If one of these table does have any data for the day (because the query is looking for date() today’s entry) then I get errors in the report.
In a different forum I was suggested to add relationships, which I did, however the relation ships did not help because if one of the tables has no data for the day, the other fails.
I am new with access and would appreciate any help/suggestions.
Thanks.

 

[MSAccessRookie]

Hello.
I've created a DAILY report to show data (text) entered into a table and I need help with it. I tried searching the forum and could not find any answers.
I want the text box in the report to get data from 3 queries which have a date() criteria.
P1 and P2 are queries of the original table.
I tried formulas like:
=[P1.SKU] & " " & [P1.Notes] & Chr(13) & Chr(10) & [P2.SKU] & " " & [P2.Notes]
=IIf(IsNull([P1.SKU] & " " & [P1.Notes])," ",[P2.SKU] & " " & [P2.Notes])
=IIf(IsNull([P1.SKU]) OR IsNull([P1.Notes])," ",[P2.SKU] & " " & [P2.Notes])
The report shows all data ONLY if all 3 tables have some data (text) entered.
If one of these table does have any data for the day (because the query is looking for date() today’s entry) then I get errors in the report.
In a different forum I was suggested to add relationships, which I did, however the relation ships did not help because if one of the tables has no data for the day, the other fails.
I am new with access and would appreciate any help/suggestions.
Thanks.

Check out the Nvl() command and see if you can make that work for you.

 

[PNGBill]

Is your problem with the report data or the query that collects the data first?

Normally, you would have a query collecting the data and when all is in order, create a report from this one query.

The query, or previous ones, can us Nz to resolve Null issues. There are a few other functions that assist with Null values in records.

Can you paste some sql query code?

 

[krutoigoga]

Is your problem with the report data or the query that collects the data first?

Normally, you would have a query collecting the data and when all is in order, create a report from this one query.

The query, or previous ones, can us Nz to resolve Null issues. There are a few other functions that assist with Null values in records.

Can you paste some sql query code?

The issue is with the report. The query (P1 or P2) seems to be working as an example below is the sql:
SELECT Table_P1.Names, Table_P1.SKU, Table_P1.Notes, Table_P1.ID, Table_P1.Dates
FROM Table_P1
WHERE (((Table_P1.Dates)=Date()));
I have also attached a test database.
Thanks.

 

[krutoigoga]

In the attached file all dates are todays and you will see in the report that the formula:
=[P1.SKU] & " " & [P1.Notes] & Chr(13) & Chr(10) & [P2.SKU] & " " & [P2.Notes]
shows all the necessary fields. But as soon as I change the date in one of the tables, then I get errors.

 

[PNGBill]

You will see postings advising against using date as a field name, don't know about dates?

Have yet to open the database you posted but can you do the concatenating in the query so the report just has another field to display.

I have seen advice in what you can do in a report and sometimes it then tells you it is better to do as much as you can in the query first.

Reports are great with totals and grouping.

 

[boblarson]

First, your links are wrong in the report's query. It should be thus:

Second, your criteria (Date()) means that ONLY records with today's date will show up. Is that what you want?

 

[krutoigoga]

First, your links are wrong in the report's query. It should be thus:

Second, your criteria (Date()) means that ONLY records with today's date will show up. Is that what you want?

Yes this is for a daily report so I only want records that were entered today, i will also add another criteria later for the name. I will try the relationship above and get back to you.
Thanks.

 

[krutoigoga]

Thanks guys.
I think this issue has been resolved.
The combination of concatenating in the query and the "proper" relation ships have reolved the issue.
Thanks again.