Another #Error question

[Guest]

I am using
=DLookUp("[ProductName]","[ProductsTTL]","[ProductId]=" & [CALHProductID])

I tried =Nz(DLookUp("[ProductName]","[ProductsTTL]","[ProductId]=" &
[CALHProductID]))

But it didn't work to supress the #Error in the new record field and in any
field that doesn't have a value

Tried changing the CALHProductID which is a number to default value 0 and
then put nothing in default value but nothing changes it.

Any suggestions?
Thanks!!

 

[Allen Browne]

If you open this table in design view, what type of field is ProductID?

a) Number field (including AutoNumber):
===============================
When CALHProductID is null, the 3rd argument resolves to:
[ProductId] =
Naturally, that's not correctly formed. To avoid the error, use Nz() to
supply some value so the 3rd string is complete. Example:
=DLookUp("ProductName", "ProductsTTL", "[ProductId] = " &
Nz([CALHProductID],0))

b) Text field:
=========
You need to add quote delimters around the value you are concatenating into
the 3rd argument:
=DLookUp("ProductName", "ProductsTTL", "[ProductId]=""" & [CALHProductID] &
"""")

c) ProductId and CALHProductID are different types.
========================================
This is not going to work. You are matching apples against oranges.

d) No records in the form
===================
A special problem arises in a form when there is no data, and no new records
can be added. The detail section goes completely blank, and any controls in
other sections are likely to generate #Error. More info:
http://allenbrowne.com/bug-06.html

 

[Guest]

Once again, Thank you for the correct answer and the REASON...!
It is so much more helpful to understand WHY something is happening rather
than just how to fix it.

lmv

If you open this table in design view, what type of field is ProductID?

a) Number field (including AutoNumber):
===============================
When CALHProductID is null, the 3rd argument resolves to:
[ProductId] =
Naturally, that's not correctly formed. To avoid the error, use Nz() to
supply some value so the 3rd string is complete. Example:
=DLookUp("ProductName", "ProductsTTL", "[ProductId] = " &
Nz([CALHProductID],0))

b) Text field:
=========
You need to add quote delimters around the value you are concatenating into
the 3rd argument:
=DLookUp("ProductName", "ProductsTTL", "[ProductId]=""" & [CALHProductID] &
"""")

c) ProductId and CALHProductID are different types.
========================================
This is not going to work. You are matching apples against oranges.

d) No records in the form
===================
A special problem arises in a form when there is no data, and no new records
can be added. The detail section goes completely blank, and any controls in
other sections are likely to generate #Error. More info:
http://allenbrowne.com/bug-06.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am using
=DLookUp("[ProductName]","[ProductsTTL]","[ProductId]=" & [CALHProductID])

I tried =Nz(DLookUp("[ProductName]","[ProductsTTL]","[ProductId]=" &
[CALHProductID]))

But it didn't work to supress the #Error in the new record field and in
any
field that doesn't have a value

Tried changing the CALHProductID which is a number to default value 0 and
then put nothing in default value but nothing changes it.

Any suggestions?
Thanks!!