Getting #Error with nz(Dlookup

Question

I keep getting a #Error when using a Dlookup, even though I've incorporated an "NZ"

Here is the formula I'm using:

Actual Servicing: nz(DLookUp("["+[tbl_Program_Types].[srp grid]+" "+Replace(Format([tbl_daily_lock_status_report].[Rate],"0.000"),".","#")+IIf([tbl_Program_Types].[srp grid] Like "*SRPG*"," 44BPS"," 25BPS")+"]","tbl_"+[tbl_Program_Types].[srp grid],"date = #"+Format([tbl_daily_lock_status_report].[Trade_Settlement Date],"mm/dd/yyyy")+"#"),0)

Answer 1

I'm getting the #Error in instances where the "Trade Settlement Date" field is null.  Do I have the nz in the wrong place?  How can I add the nz to both the dlookup and where the "Format([tbl_daily_lock_status_report].[Trade_Settlement Date]" is?

Answer 2

Essentially it's doing a couple lookups to identify a specific interest rate based on the "Trade Settlement Date" for the given account type found in the "SRP Grid".  I can say that the formula works for any instance where there is a number found but it's the places where there isn't an answer found and I'm trying to have it default to a "0" where it's giving me the #Error.  Sorry, I know it's a little confusing but just know that the way it is that it does in fact work where I need it to but it's where there should just be a null value (0) that I get the error.

Answer 3

I keep getting a #Error when using a Dlookup, even though I've incorporated an "NZ"

 

Here is the formula I'm using:

Actual Servicing: nz(DLookUp("["+[tbl_Program_Types].[srp grid]+" "+Replace(Format([tbl_daily_lock_status_report].[Rate],"0.000"),".","#")+IIf([tbl_Program_Types].[srp grid] Like "*SRPG*"," 44BPS"," 25BPS")+"]","tbl_"+[tbl_Program_Types].[srp grid],"date = #"+Format([tbl_daily_lock_status_report].[Trade_Settlement Date],"mm/dd/yyyy")+"#"),0)

Okay, perhaps you can explain what you're trying to get because this Dlookup is horribly, horribly wrong but I can't make out what it is you really want.