Linking two tables with null Date/timefields

[lauriesamh]

I'm trying to audit two tables to ensure the records in one table are in the other. My problem is when a date/time field has null values in both tables the records don't match. Any ideas?

SELECT
[SHS_IHN_ELIG - July & Aug].FileDate,
[T834chg_wkly_20070709_0704_001-flat date formated].FileDt, [T834chg_wkly_20070709_0704_001-flat date formated].[Subscriber ID], [SHS_IHN_ELIG - July & Aug].ReasonCode,
[T834chg_wkly_20070709_0704_001-flat date formated].[Maintenance Code], [SHS_IHN_ELIG - July & Aug].BeginDate,
[T834chg_wkly_20070709_0704_001-flat date formated].[Start Date], [SHS_IHN_ELIG - July & Aug].EndDate,
[T834chg_wkly_20070709_0704_001-flat date formated].[End Date],

FROM [SHS_IHN_ELIG - July & Aug] INNER JOIN [T834chg_wkly_20070709_0704_001-flat date formated] ON ([SHS_IHN_ELIG - July & Aug].EndDate = [T834chg_wkly_20070709_0704_001-flat date formated].[End Date])
AND ([SHS_IHN_ELIG - July & Aug].BeginDate = [T834chg_wkly_20070709_0704_001-flat date formated].[Start Date])
AND ([SHS_IHN_ELIG - July & Aug].Zip = [T834chg_wkly_20070709_0704_001-flat date formated].Zip)
AND ([SHS_IHN_ELIG - July & Aug].State = [T834chg_wkly_20070709_0704_001-flat date formated].State)
AND ([SHS_IHN_ELIG - July & Aug].City = [T834chg_wkly_20070709_0704_001-flat date formated].City)
AND ([SHS_IHN_ELIG - July & Aug].RP_LastName = [T834chg_wkly_20070709_0704_001-flat date formated].QDLastName) AND ([SHS_IHN_ELIG - July & Aug].DOB = [T834chg_wkly_20070709_0704_001-flat date formated].[DOB formatted])
AND ([SHS_IHN_ELIG - July & Aug].SBSB_ID = [T834chg_wkly_20070709_0704_001-flat date formated].[Subscriber ID])
AND ([SHS_IHN_ELIG - July & Aug].ReasonCode = [T834chg_wkly_20070709_0704_001-flat date formated].[Maintenance Code])
AND ([SHS_IHN_ELIG - July & Aug].LastName = [T834chg_wkly_20070709_0704_001-flat date formated].LastName)
AND ([SHS_IHN_ELIG - July & Aug].FirstName = [T834chg_wkly_20070709_0704_001-flat date formated].[First Name]);

 

[JerryKlmns]

If it 's NULL then NULL compared to NULL always returns false. Since you don't know the value that should be there, if you use a dummy one (1901-01-01) would that be acceptable?

 

[PHV]

Use the Nz function ?
ON (Nz([SHS_IHN_ELIG - July & Aug].EndDate,0) = Nz([T834chg_wkly_20070709_0704_001-flat date formated].[End Date],0))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886