SOLVED

#Error on New Record

Super Contributor

Experts, I am trying to get rid of the #error on the new row in my datasheet (pic below).  

I am using this formula:

=iif(IsError(DLookUp("[Rate]","qryPricingNow","[ProjID]=" & [ProjID])),"",DLookUp("[Rate]","qryPricingNow","[ProjID]=" & [ProjID])

 

However, the #Error still shows on the new record line

Not sure if my approach is correct?

 

here is qryPricing if needed:

SELECT tblPricing.ID, tblPricing.ProjID, tblPricing.Rate, tblPricing.PeriodID, tblDates.DateStart, tblDates.DateEnd, tblDates.NameOfDate
FROM tblPricing INNER JOIN tblDates ON tblPricing.PeriodID = tblDates.ID
WHERE (((tblDates.DateStart) Between Date() And (tblDates.DateStart)<[DateEnd]));

 

thank you

Tony2021_0-1651110594322.png#error is on the new record

 

2 Replies
best response confirmed by Tony2021 (Super Contributor)
Solution
try this:

=DLookUp("[Rate]","qryPricingNow","[ProjID]=" & Nz([ProjID], 0))
Perfect! Much cleaner too. thank you very much.