Blank Values are not show as result in query

[dhen21dx]

Hello,

I have created a query to get the only equal on material and plant, however, the result does not include Null value from plant, can somebody help me how to deal with it?

Table 1
Material	Plant	Dest
CDFSS	US	HK
CDFSS		HK
CDFSS	PH	HK
Table 2
Material	Plant	Qty	Date	CreatedBy
CDFSS	US	6​	1/21/2021​	User1
CDFSS		9​	8/29/2021​	User1
CDFSS	TW	10​	12/2/2021​	User1
INNER JOIN	Material	Table 1
		Table 2
	Plant	Table 1
		Table 2

 

[Joe4]

If you want to return ALL values from one table, and return the matches to another, you do not want to do an INNER JOIN. An INNER JOIN will only return matches.
You want a LEFT JOIN (or sometimes known as a LEFT OUTER JOIN). This link will show you how to do that: Access - Create Queries with Outer Joins

Then, if you want to return a 0 in the unmatched rows instead of a blank space, you can use the NZ function, i.e.

UpdatedQty: Nz([Qty],0)+0

See: MS Access: Nz Function

 

[dhen21dx]

Hi Joe,

I just want to show the item equals to each other, however, blanks or null in plants are not showing in the result.

 

[Joe4]

I just want to show the item equals to each other, however, blanks or null in plants are not showing in the result.

OK, if you only want the matching ones, then you would just use the standard Inner Join.
I think that maybe you want to join on BOTH the Material and Plant fields. But if that is so, then you may have an issue with the records that are missing "Plant" values". You may need to use the NZ function to populate empty "Plant" field in order to complete the join.

If that is the case, then I would just Join on the Material field, and then add a condition in your WHERE clause that looks something like this:

WHERE Nz([Table1]![Plant],"ZZZ") = Nz([Table2]![Plant],"ZZZ")

If you still cannot figure out how to write it, please show us an example of EXACTLY how you want the results of the query to look, based on your data samples of the two tables.