Find Duplicates query is ignoring null values

[dawnd3]

I am trying to find all records that match in several different fields. I can see clearly that there are many records that match in all of these fields but the query comes back with zero results. When I remove the date field from the query it works. In this set of records the date field has a lot of blank entries. How do I get that to show those records as matches when the date feild is null?

thanks,

Dawn

http://www.calliopeconsulting.net

 

[Melagan]

Have a look at the Nz() function. If you post your SQL, we could probably help out with more specific examples.

~Melagan
______
"It's never too late to become what you might have been.

 

[dawnd3]

Thanks Melagan...Here is my SQL (I don't know SQL very well, I just use the find duplicates wizard.

SELECT FalseClaimLog.From, FalseClaimLog.DocType, FalseClaimLog.[FWS ProjNo], FalseClaimLog.To, FalseClaimLog.Date, FalseClaimLog.ID1
FROM FalseClaimLog
WHERE (((FalseClaimLog.From) In (SELECT [From] FROM [FalseClaimLog] As Tmp GROUP BY [From],[DocType],[FWS ProjNo],[To],[Date] HAVING Count(*)>1 And [DocType] = [FalseClaimLog].[DocType] And [FWS ProjNo] = [FalseClaimLog].[FWS ProjNo] And [To] = [FalseClaimLog].[To] And [Date] = [FalseClaimLog].[Date])))
ORDER BY FalseClaimLog.From, FalseClaimLog.DocType, FalseClaimLog.[FWS ProjNo], FalseClaimLog.To, FalseClaimLog.Date;

Thanks,

Dawn

http://www.calliopeconsulting.net

 

[Melagan]

You got a subquery out of a find-duplicates wizard?

Anyway...try revising your SQL as such:

SELECT f.From
, f.DocType
, f.[FWS ProjNo]
, f.To
, Nz(f.Date) as myDate
, f.ID1

FROM FalseClaimLog as f

WHERE (((f.From) In (SELECT [From] FROM FalseClaimLog As Tmp GROUP BY [From],[DocType],[FWS ProjNo],[To], Nz([Date]) as myDate2 HAVING Count(*)>1  And [DocType] = [f].[DocType] And [FWS ProjNo] = [f].[FWS ProjNo] And [To] = [f].[To] And Nz([Date]) = Nz(f.Date))))

ORDER BY f.From, f.DocType, f.[FWS ProjNo], f.To, Nz(f.Date);

If this doesn't work, would you post some sample data and expected results?

~Melagan
______
"It's never too late to become what you might have been.