Creating a query to catch exceptions in tables?

[TKTheKid]

Hello all,

I wrote a query to subtract the quantity amounts in each table to find ones that didn't match and then only print those records.

I also had the query try to spit out exceptions if data was only in one table or the other.

When I run queryDifference it now shows the two fake exception records I expected to see from each table, but why doesn't the difference appear in the Difference column? I'm guessing because it's trying to subtract from NULL, but is there any way that the difference for those exceptions records could still appear in the Difference column? I would need that to create the report at the end.

Also, speaking of the report, when I try to access my report reportDifference, I get the following message now:

"The specified field 'tableKWIData.QUANTITY' could refer to more than one table listed in the FROM clause of your SQL statement."

What does that mean? 'tableKWIData.QUANTITY' only refers to one table, doesn't it?

Tony

 

[boblarson]

You need the NZ function to handle nulls

Difference: Nz([tableKWIData].[Quantity],0)-Nz([tableWarehouseData].[Quantity],0)

 

[TKTheKid]

Thanks boblarson! That worked. However, I still can't access the report. Please see below:

"Also, speaking of the report, when I try to access my report reportDifference, I get the following message now:

'The specified field 'tableKWIData.QUANTITY' could refer to more than one table listed in the FROM clause of your SQL statement.'

What does that mean? 'tableKWIData.QUANTITY' only refers to one table, doesn't it?"

 

[boblarson]

This should work. Remember to change the control sources in the report to match. But also you are missing several fields from the query for the report.