Using DLookup with NZ in query.

[JRodko]

Hello,



I am trying to use a combination of the Nz function and DlookUp function to find numerical gaps in data and append the offending rows to another table.

I would like the query to return rows where the "From Depth" does not match the "To Depth" in the previous record. Currently the query returns all data and not only the records I want to see.

I have attached a sample database but the SQL statement for the entire query is

"
INSERT INTO tbl_QAQC_Intervals ( [HOLE ID], [FROM], [TO], [TABLE] )SELECT Geotechnical.GEOT_Hole_ID, Geotechnical.GEOT_From_m, Geotechnical.GEOT_To_m, "GEOTECHNICAL GAP" AS [Table]
FROM tblSelectedDrillHoleandDeclination INNER JOIN Geotechnical ON tblSelectedDrillHoleandDeclination.hole_NUM = Geotechnical.GEOT_Hole_ID
WHERE (((Nz([GEOT_From_m]<>DLookUp("[GEOT_To_m]","Geotechnical","GEOT_ID=[Geotechnical]![GEOT_ID]-1"),False))=False))
ORDER BY Geotechnical.GEOT_From_m;
"

And the formula I'm using to select rows is

"Expr1: Nz([GEOT_From_m]<>DLookUp("[GEOT_To_m]","Geotechnical","GEOT_ID=[Geotechnical]![GEOT_ID]-1"),False)"

Where GEOT_From_m is the "From Depth", GEOT_To_m is the "To Depth", GEOT_Hole_ID is a project ID, and "GEOT_ID" is an autonumber which I am using to compare the depths from each record.

The data is entered in a form however referencing the table itself is fine I believe. I got the idea to use NZ from Allen Brown.

Thank you.Gaps.zip

[CJ_London]

You would be better left joining the table to itself - copy and paste this sql

INSERT INTO tbl_QAQC_Intervals ( [HOLE ID], [FROM], [TO], [TABLE] )
SELECT Geotechnical.GEOT_Hole_ID, Geotechnical.GEOT_From_m, Geotechnical.GEOT_To_m, "GEOTECHNICAL GAP" AS [Table]
FROM (tblSelectedDrillHoleandDeclination INNER JOIN Geotechnical ON tblSelectedDrillHoleandDeclination.hole_NUM = Geotechnical.GEOT_Hole_ID) LEFT JOIN Geotechnical AS Geotechnical_1 ON (Geotechnical.GEOT_Hole_ID = Geotechnical_1.GEOT_Hole_ID) AND (Geotechnical.GEOT_From_m = Geotechnical_1.GEOT_To_m)
WHERE (((Geotechnical_1.GEOT_To_m) Is Null))
ORDER BY Geotechnical.GEOT_From_m;

Note that Table is a reserved word, use it at your own risk

[JRodko]

Wow. Thank you very much.

How does this work without referencing the previous record?

[CJ_London]

your requirement is to find 'Froms' without a matching 'To'. You have no guarantee that you don't have missing ID numbers nor that the 'previous' ID contains the matching To. In databases there is no such thing as 'previous' or 'next' without specifying an order. So the usual way is to find a record with the highest ID that is less than the current ID

Also just realised you are also using 'From' as a field name - another reserved word. Before you start to suffer grief and waste a lot of time trying to solve an issue with a misleading error - see this link about reserved words
https://support.office.com/en-us/art...9-f855bdd9c5a2

Note that I have seen instances where using square brackets does not always solve the issue

[JRodko]

I had not thought of solving the problem that way. Thank you very much!!!

[JRodko]

One small modification needed. The Query returns the starting record every time even though there is no error. This happens because there is no matching (To) record for the lowest number (From).

The number could be anything and will often not be the same otherwise I'd set a simple criteria like From <> 0.

Any input on skipping the first error would be great. I am thinking Dmin but i think that would search the entire table and not just the query selection?

[CJ_London]

you can use dmin - with a where clause that identifies the hole_id, or you can use another query which will generally be quicker - copy and paste this code

INSERT INTO tbl_QAQC_Intervals ( [HOLE ID], [FROM], [TO], [TABLE] )
SELECT Geotechnical.GEOT_Hole_ID, Geotechnical.GEOT_From_m, Geotechnical.GEOT_To_m, "GEOTECHNICAL GAP" AS [Table]
FROM ((tblSelectedDrillHoleandDeclination INNER JOIN Geotechnical ON tblSelectedDrillHoleandDeclination.hole_NUM = Geotechnical.GEOT_Hole_ID) LEFT JOIN Geotechnical AS Geotechnical_1 ON (Geotechnical.GEOT_From_m = Geotechnical_1.GEOT_To_m) AND (Geotechnical.GEOT_Hole_ID = Geotechnical_1.GEOT_Hole_ID)) INNER JOIN (SELECT GEOT_Hole_ID, Min(GEOT_From_m) as minFrom FROM Geotechnical GROUP BY  GEOT_Hole_ID)  AS Geotechnical_Min ON tblSelectedDrillHoleandDeclination.hole_NUM = Geotechnical_Min.GEOT_Hole_ID
WHERE (((Geotechnical_1.GEOT_To_m) Is Null) AND ((Geotechnical.GEOT_From_m)<>[minfrom]))
ORDER BY Geotechnical.GEOT_From_m