Table Relationship with Blank Values

[mjoachim]

I am working on a query that'll find invalid entries. My users enter payroll data into a form and the form only allows them to enter active Cost Code & Job/Sub Job combinations, but often enough they change something after initial entry that results in an invalid combination.

In my query, I have a relationship created between my Data Import(Payroll entries) table and a table of current/active cost codes (see attached). With that relationship, I pull all entries from my Data Import table and only the Cost code field from the Cost Codes table if there is a match. This allows me to see if a match is missing and therefore invalid.

This works great if an entry has a sub job. The problem is if there is no sub job, that field in both tables is blank which doesn't appear to create a match for the relationship.

Is there some way to specify in the relationship that blank should match blank, or add a Nz( ,0) statement of sorts? I don't really want to store a 0 value in the tables, so am seeking out a way to create a match for comparison only.

Thanks!

[jforbes]

This won't be the most efficient Query, but it will probably do what you want:

SELECT *
FROM [Data Import] 
LEFT JOIN [Cost Codes] 
ON  [Data Import].[Job Number] = [Cost Codes].JobNumber
AND [Data Import].[Cost Code] = [Cost Codes].CostCode
AND [Data Import].[Sub Job] & '' = [Cost Codes].SubJobNumber &''

I think the same thing can be accomplished with a couple NZ()s:

Nz([Data Import].[Sub Job], "") = Nz([Cost Codes].SubJobNumber, "")

Maybe you can tell us which works better. =)

[mjoachim]

I would say they both work equally as well. Slow and inefficient as you expected, but nonetheless accomplishes what I asked.

As always, thank you for your help jforbes!