So the issue is I have two tables and the field SERVICE TYPE in both tables can contain null, so when I try matching it I get nothing back, so how can fix this ??? I know I can use the Nz on my first select however this did not work Nz(rst![SERVICE TYPE], Null)
Set rst = CurrentDb.OpenRecordset("SELECT * FROM AppendZips;")
Set rst2 = CurrentDb.OpenRecordset("SELECT DISTINCT * FROM Master_Database WHERE [HG ID] = " & HGID & " AND [SERVICE TYPE] = '" & rst![SERVICE TYPE] & "' ;")
1) You are using the Nz() function incorrectly. Using the function already presumes that the value may be Null or Zero (hence the name, Nz). The value after the comma is meant to "un-zero" and "un-null" the null/zero value, and therefore must contain a valid value. In the case of a Null string value, you would normally use " "; in the case of a Null numeric value, you would use 0.
Therefor, change the formula to
Nz(rst![SERVICE TYPE], " ")
Nz(rst![SERVICE TYPE], 0)
depending on the data type of SERVICE TYPE.
2) If you are joining your two tables on the SERVICE TYPE field, you will have problems, because the JOIN takes place BEFORE the Nz() function does its work. In order to join on the SERVICE TYPE field, you first have to run preliminary queries on each table separately, using the Nz() function to change the null value to a proper value, and then using the query joining the two.