Outer join query returning #Error rather than null

[Alephu5]

I have been battling with this problem for hours, and would be incredibly grateful if someone could enlighten me as to what I am doing wrong. I want to build a query that displays identifying records from one query, excluding those that are in another query. Internet research and the unmatched query wizard suggested that the solution would be to use a left outer join, with the query on the right having a criterion of "Is Null". This query (shown below) returned a data mismatch error, although the root tables of both are text format.

SELECT qryFBatchMakeup.BatchNumber
FROM qryFBatchMakeup 
LEFT JOIN qryReprocessedBatches
ON qryFBatchMakeup.BatchNumber = qryReprocessedBatches.BatchNumber 
WHERE (((qryReprocessedBatches.BatchNumber) Is Null));

To try and find out the cause, I removed the filter criterion and added the field 'qryReprocessedBatches.BatchNumber'.The records that were in qryFBatchMakeup but not qryReprocessedBatches were displayed as '#Error#', although I cannot find any logical reason why this isn't null. Anyone have any ideas why this is not working or know a way I could investigate the nature of the errors?

 

[Alephu5]

It displays a list of all the records that are present in 'qryFBatchMakeup.BatchNumber' without any problems, it is identical to

SELECT qryFBatchMakeup.BatchNumber
FROM qryFBatchMakeup;

 

[MSAccessRookie]

I have been battling with this problem for hours, and would be incredibly grateful if someone could enlighten me as to what I am doing wrong. I want to build a query that displays identifying records from one query, excluding those that are in another query. Internet research and the unmatched query wizard suggested that the solution would be to use a left outer join, with the query on the right having a criterion of "Is Null". This query (shown below) returned a data mismatch error, although the root tables of both are text format.

SELECT qryFBatchMakeup.BatchNumber
FROM qryFBatchMakeup 
LEFT JOIN qryReprocessedBatches
ON qryFBatchMakeup.BatchNumber = qryReprocessedBatches.BatchNumber 
WHERE (((qryReprocessedBatches.BatchNumber) Is Null));

To try and find out the cause, I removed the filter criterion and added the field 'qryReprocessedBatches.BatchNumber'.The records that were in qryFBatchMakeup but not qryReprocessedBatches were displayed as '#Error#', although I cannot find any logical reason why this isn't null. Anyone have any ideas why this is not working or know a way I could investigate the nature of the errors?

I believe that they Query is doing exactly what you are asking it to do. The problem is that it depends on two additional Queries, at least one of which is NOT doing what you want it to do. The problem is most likely in the Sub-Query. Find it and the rest of the issues go will away.

 

[Alephu5]

I believe that they Query is doing exactly what you are asking it to do. The problem is that it depends on two additional Queries, at least one of which is NOT doing what you want it to do. The problem is most likely in the Sub-Query. Find it and the rest of the issues go will away.

I've managed to isolate the field in a sub query that is causing the data mismatch error, but I am still unsure exactly what the problem is. The field 'qryReprocessedBatches.BatchNumber' is generated from a query (although I've made a table to eliminate the influence of sub-queries) with the following code:

"F" & [RawNumber] & Chr[HopperASCII]

Where RawNumber is a four-digit number and HopperASCII is an integer. My guess is that the error is occuring because the outermost query is looking for the RawNumber and HopperASCII code that doesn't exist. It doesn't seem worth wasting more time on this so I've decided to write a small bit of VBA code to create a table whenever the database is opened.

EDIT: Okay, I did decide to waste more time. My guess to the cause of the problem was correct. Rather than combining the RawNumber and HopperASCII into a single field, I kept them in separate fields and modified the original query to have two outer joins and "Is Null" criteria. It now works fine.