UPDATE hsinventory SET hsinventory.[Serial Number] = Trim("" & [Serial Number]);
UPDATE tbl_Matrix_master SET tbl_Matrix_master.serial_number = Trim("" & [serial_number]);
It works with Access 2000. Trims out the spaces. It also avoids the null errors and other stuff by padding a blank to the text before trimming it.
I created two tables named the same as yours, entered a few serial numbers into each table, had a few that matched, and the ones that matched came up in that query before and after the trim. I made sure that some columns had spaces after them. I think Access ignores the extra spaces.
Any Relationships you have set on the two tables? I can't think of anything else that could hose it up right now.