Hashed file lookups are by primary key. Primary keys can not be null (by definition). Likewise the primary keys in your tables can not be null. Therefore how are you getting nulls?
You load the key value from your table into a hashed file. You perform a lookup against the key. It is either found (an update is required) or it is not found (an insert is required).
Are you claiming that you have target tables with keys that are not null?
The other comparison columns are, of course, tricker, in that you must handle nulls there. Something like the following to test for a match
Code: Select all
If Not(IsNull(source.MyCol)) And Not(IsNull(lookup.MyCol)) Then (source.MyCol = lookup.MyCol) Else (IsNull(source.MyCol) And IsNull(lookup.MyCol))
You definitely do not need routines in this case.
But if you did, a generic routine would have the form:
Code: Select all
FUNCTION IsEqual(Arg1, Arg2)
Arg1IsNull = IsNull(Arg1)
Arg2IsNull = IsNull(Arg2)
If Not(Arg1IsNull) And Not(Arg2IsNull)
Then
Ans = (Arg1 = Arg2)
End
Else
Ans = (Arg1IsNull And Arg2IsNull)
End
RETURN(Ans)
The logic is identical to the expression above - it's just laid out differently!
This function returns true if both values are not null and equal or if both arguments are null, and returns false otherwise. The two assignment statements have what's called a logical, or Boolean, expression on the right hand side; each locical expression can only result in a true/false value.
The IsNull function results were loaded into local variables solely to avoid evaluating each twice.