Null records return #error with dlookup query

Question

I have a query that does a Dlookup on certain fields. One of the fields [Restriction_Type] in (Dlookup1) returns #Error on certain records, which I think is because some of the records in field: [RestrictionID] (Dlookup2) are blank: So Where would I put the Nz function within my Dlookup string so that the query can deal with occasional blank fields and not return #Error?

This is the Dlookup that returns the occasional #Error

**(Dlookup 1)**Restriction_Type: DLookUp("Restriction_Type","tbl_Restriction_Type","Restriction_Type_ID=" & [RestrictionID])

This is the Dlookup that the above line makes reference to with [RestrictionID]

(Dlookup 2)

RestrictionID: DLookUp("Restriction_ID","tbl_Road_Restricts","Road_Restrict_ID_From=" & [tbl_Street_Names_Offs].[Street_Off_ID_From] & "  Or Road_Restrict_ID_To =" & [Street_Off_ID_To])

This is my SQL:

SELECT tbl_Street_Names_Offs.StreetName_Off_ID, tbl_Street_Names_Offs.Street_Off_ID_From, tbl_Street_Names.StreetName AS StreetName_From, tbl_Street_Names_Offs.Street_Off_ID_To, tbl_Street_Names_Offs.Postcode, DLookUp("Restriction_Type","tbl_Restriction_Type","Restriction_Type_ID=" & Nz([RestrictionID])) AS Restriction_Type, DLookUp("Restriction_ID","tbl_Road_Restricts","Road_Restrict_ID_From=" & [tbl_Street_Names_Offs].[Street_Off_ID_From] & "  Or Road_Restrict_ID_To =" & [Street_Off_ID_To]) AS RestrictionID, DLookUp("Direction_From","tbl_Road_Restricts","Road_Restrict_ID_From=" & [tbl_Street_Names_Offs].[Street_Off_ID_From] & "  AND Road_Restrict_ID_To =" & [Street_Off_ID_To]) AS Direction_From, DLookUp("Direction_To","tbl_Road_Restricts","Road_Restrict_ID_From=" & [tbl_Street_Names_Offs].[Street_Off_ID_From] & "  AND Road_Restrict_ID_To =" & [Street_Off_ID_To]) AS Direction_To, DLookUp("Road_Name_Upto_ID","tbl_Road_Restricts","Road_Restrict_ID_From=" & [tbl_Street_Names_Offs].[Street_Off_ID_From] & "  AND Road_Restrict_ID_To =" & [Street_Off_ID_To]) AS Upto_ID, DLookUp("StreetName","tbl_Street_Names","StreetNameID =" & Nz([Upto_ID],0)) AS Road_Name_Upto, tbl_Street_Names_Offs.Street_Note, tbl_Street_Names.Waypoint, tbl_Street_Names.Non_Street

FROM tbl_Street_Names RIGHT JOIN tbl_Street_Names_Offs ON tbl_Street_Names.StreetNameID = tbl_Street_Names_Offs.Street_Off_ID_From

ORDER BY DLookUp("Road_Name_Upto_ID","tbl_Road_Restricts","Road_Restrict_ID_From=" & [tbl_Street_Names_Offs].[Street_Off_ID_From] & "  AND Road_Restrict_ID_To =" & [Street_Off_ID_To]) DESC;

Answer 1

You would need a query as the source if you wanted to merge the two into one DLookup.

So if you had a saved query like the following, you could apply all the criteria in the DLookup to the query

SELECT tbl_Restriction_Type.Restriction_Type

, tbl_Restriction_Type.Restriction_Type_ID

, tbl_Road_Restricts.Restriction_ID

, tbl_Road_Restricts.Road_Restrict_ID_FROM

, tbl_Road_Restricts.Road_Restrict_ID_TO

FROM tbl_Restriction_Type INNER JOIN tbl_Road_Restricts

ON tbl_Restriction_Type.RestrictionTypeID = tbl_Road_Restricts.RestrictionID

The DLookup might look like:

DLookup("Restriction_Type","NameOfSavedQuery","Road_Restrict_ID_From=" & [tbl_Street_Names_Offs].[Street_Off_ID_From] & "  Or Road_Restrict_ID_To =" & [Street_Off_ID_To])

---

John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County

Answer 2

You could try the following.  I am guessing that Restriction_ID is a number field.

**(Dlookup 1)**Restriction_Type: DLookUp("Restriction_Type","tbl_Restriction_Type","Restriction_Type_ID=" & NZ(DLookUp("Restriction_ID","tbl_Road_Restricts","Road_Restrict_ID_From=" & [tbl_Street_Names_Offs].[Street_Off_ID_From] & "  Or Road_Restrict_ID_To =" & [Street_Off_ID_To]),0))

That said, why do you need to use DLOOKUP at all.  Can't you include the tables in your query?

I suspect that many DLookups would have a significant effect on query performance.

---

John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County

John,

That did the trick. To reference to your underlying question; "Can't you include the tables in your query?". I would like to come back to you at some point when I can gather my thoughts and try to give a proper explanation of what I am trying to do; suffice it to say, it is (to me) fiendishly complex and involves multiple forms and various joins, both Left and Right.

 Also, have you 'merged' (nested?) the two Dlookups into one whole Dlookup?

Answer 3

Use the Nz function to prevent the error.

Restriction_Type: Nz(DLookUp("Restriction_Type","tbl_Restriction_Type","Restriction_Type_ID=" & [RestrictionID]),0)

RestrictionID: Nz(DLookUp("Restriction_ID","tbl_Road_Restricts","Road_Restrict_ID_From=" & [tbl_Street_Names_Offs].[Street_Off_ID_From] & "  Or Road_Restrict_ID_To =" & [Street_Off_ID_To]),0)

---

Jeanette Cunningham MVP(Access) Pakenham, Victoria Australia

Answer 4

You could try the following.  I am guessing that Restriction_ID is a number field.

**(Dlookup 1)**Restriction_Type: DLookUp("Restriction_Type","tbl_Restriction_Type","Restriction_Type_ID=" & NZ(DLookUp("Restriction_ID","tbl_Road_Restricts","Road_Restrict_ID_From=" & [tbl_Street_Names_Offs].[Street_Off_ID_From] & "  Or Road_Restrict_ID_To =" & [Street_Off_ID_To]),0))

That said, why do you need to use DLOOKUP at all.  Can't you include the tables in your query?

I suspect that many DLookups would have a significant effect on query performance.

---

John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County