comparison misses empty strings 1

[diwin]

Hi.

I am trying to compare the fields of a record that has been added to an "order" with the analogous fields in the source data table that the ordering process draws them from. This way, I can recognize when one "order" has been created with a different version of the data in the source data table. But some of the fields can be left empty in either location. When I compare all the pertinent fields, changes are caught, but not when the field in either the source or "order" table is blank.

I have to catch these differences, as they define versions. i.e. RoadName = "" compared to RoadName = "Victoria Parkway"

SELECT A.ID, A.SiteListID, A.Subwatershed, A.Site, A.WatercourseName, A.Easting, A.Northing, A.Township, A.Lot, A.Con, A.RoadName
FROM qryFillTempTables AS B INNER JOIN qryTempSite AS A ON B.SiteID = A.SiteListID
WHERE (((A.SiteListID)=B.SiteID) AND ((A.Subwatershed)<>B.Subwatershed)) OR (((A.SiteListID)=B.SiteID) AND ((A.Site)<>B.SiteCode)) OR (((A.SiteListID)=B.SiteID) AND ((A.WatercourseName)<>B.WatercourseName)) OR (((A.SiteListID)=B.SiteID) AND ((A.Easting)<>B.Easting)) OR (((A.SiteListID)=B.SiteID) AND ((A.Northing)<>B.Northing)) OR (((A.SiteListID)=B.SiteID) AND ((A.Township)<>B.Township)) OR (((A.SiteListID)=B.SiteID) AND ((A.Lot)<>B.Lot)) OR (((A.SiteListID)=B.SiteID) AND ((A.Con)<>B.Con)) OR (((A.SiteListID)=B.SiteID) AND ((A.RoadName)<>B.RoadName));

Daniel Dillon
O o . (<--- brain shrinking at rate shown.)

 

[diwin]

OK -- I had to modify the query, just adding identical fields from table B to catch the Distinctness, but the problem persists. The comparison of a field is ignored when one is blank. How do I get it to recognize that there is a difference between the blank and non-blank field???

SELECT DISTINCT A.ID, A.SiteListID, A.Subwatershed, A.Site, A.WatercourseName, A.Easting, A.Northing, A.Township, A.Lot, A.Con, A.RoadName, B.SiteCode, B.Subwatershed, B.WatercourseName, B.Easting, B.Northing, B.Township, B.Lot, B.Con, B.RoadName
FROM qryFillTempTables AS B INNER JOIN qryTempSite AS A ON B.SiteID=A.SiteListID
WHERE (((A.SiteListID)=B.SiteID) And ((A.Subwatershed)<>B.Subwatershed)) Or (((A.SiteListID)=B.SiteID) And ((A.Site)<>B.SiteCode)) Or (((A.SiteListID)=B.SiteID) And ((A.WatercourseName)<>B.WatercourseName)) Or (((A.SiteListID)=B.SiteID) And ((A.Easting)<>B.Easting)) Or (((A.SiteListID)=B.SiteID) And ((A.Northing)<>B.Northing)) Or (((A.SiteListID)=B.SiteID) And ((A.Township)<>B.Township)) Or (((A.SiteListID)=B.SiteID) And ((A.Lot)<>B.Lot)) Or (((A.SiteListID)=B.SiteID) And ((A.Con)<>B.Con)) Or (((A.SiteListID)=B.SiteID) And ((A.RoadName)<>B.RoadName));

Daniel Dillon
O o . (<--- brain shrinking at rate shown.)

 

[HarleyQuinn]

If I understand what you're after you could try using an OUTER JOIN instead.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[PHV]

SELECT A.ID, A.SiteListID, A.Subwatershed, A.Site, A.WatercourseName, A.Easting, A.Northing, A.Township, A.Lot, A.Con, A.RoadName, B.SiteCode, B.Subwatershed, B.WatercourseName, B.Easting, B.Northing, B.Township, B.Lot, B.Con, B.RoadName
FROM qryFillTempTables AS B INNER JOIN qryTempSite AS A ON B.SiteID=A.SiteListID
WHERE Nz(A.Subwatershed)<>Nz(B.Subwatershed)
 Or Nz(A.Site)<>Nz(B.SiteCode)
 Or Nz(A.WatercourseName)<>Nz(B.WatercourseName)
 Or Nz(A.Easting)<>Nz(B.Easting)
 Or Nz(A.Northing)<>Nz(B.Northing)
 Or Nz(A.Township)<>Nz(B.Township)
 Or Nz(A.Lot)<>Nz(B.Lot)
 Or Nz(A.Con)<>Nz(B.Con)
 Or Nz(A.RoadName)<>Nz(B.RoadName)

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

 

[diwin]

Thanks PH.


^
< >
V V

Daniel Dillon
O o . (<--- brain shrinking at rate shown.)