Dlookup Error (1 Viewer)

jeran042

Registered User.

Local time

Today, 08:12

Joined

Jun 26, 2017

Messages

127

• Jan 29, 2019

• #1

I have an error that I can use some help on:
I have an unbound textbox that performs a dlookup. The formula looks like this:

Code:Copy to clipboard

=Replace(Nz(DLookUp("tblInvoiceLog.NOTES","tblInvoiceLog","[VOUCHER_NUMBER]= " & [txtADJ_POLICY] & ""),""),Chr(13) & Chr(10)," ")

This works fine until there is no [VOUCHER_NUMBER] to look up. I then get a #Type! Error.

I would have thought that the Nz would negate this error (apparently I am wrong).

What can I do mitigate the error so I don’t see the #Type! Error

 

pbaldy

Wino Moderator

Staff member

Local time

Today, 08:12

Joined

Aug 30, 2003

Messages

36,124

• Jan 29, 2019

• #2

You can also wrap the field reference in the Nz() function:

Nz([txtADJ_POLICY], 0)

 

jeran042

Registered User.

Local time

Today, 08:12

Joined

Jun 26, 2017

Messages

127

• Jan 29, 2019

• #3

That seems to do it, however, probably unrelated, but I now see a "#Size!" error? Any additional advice?

 

pbaldy

Wino Moderator

Staff member

Local time

Today, 08:12

Joined

Aug 30, 2003

Messages

36,124

• Jan 29, 2019

• #4

Just when there's no value? I'd start with just the lookup, and add the replace once that works reliably.