How to join empty fields? 1

[FractalWalk]

I am trying to join two tables in an update query by matching 5 fields. The problem is that one of the fields is allowed to be blank, but I still need to match on it when the field is blank in both tables. Currently my query will not recognize a blank field match as a match.

Here is the SQL showing the 5 fields joined. How can I get a blank match to be recognized?


UPDATE [A]
INNER JOIN ON ([A].Date = .Date) AND ([A].[Band] = .[Band]) AND ([A].Company = .Company) AND ([A].State = .State) AND ([A].[SAE Suffix] = .[SAE Suffix]) AND ([A].[SAE Root] = .[SAE Root])
SET [A].Qty = !Qty;

 

[SnaveBelac]

You have a rouge "!" in your code that could be causing a problem...

You have !Qty instead of .Qty

Otherwise, are you comparing Null values with zero-length strngs ?





----------------------------
SnaveBelac - Adventurer
----------------------------

 

[cmmrfrds]

Join on the first 4 columns that have content and put the test for the blank fields in the where clause.

WHere a.field = b.field or (a.field is null and b.field is null)

 

[FractalWalk]

WHere a.field = b.field or (a.field is null and b.field is null)


That did it! Thanks.