UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Dlookup Returning #error Instead Of Null When Not Finding A Value, Access 2016    
 
   
marcuscatan
post Jan 20 2020, 09:33 AM
Post#1



Posts: 15
Joined: 20-January 20



Hello all, this is my very first post in a forum at all, I usually just search around and I'm only posting because I couldn't find answers anywhere.

I have a field box named [FormulaBox], and I have 3 other fields which use Dlookup functions from this one.

When the fields is blank the others are as well. When it finds a value, everything works well (on all 3 fields). But when it doesn't find a value (because the input in FormulaBox does not correspond to a value on the table I'm searching) my Dlookups go crazy and show #Error and keep blinking like crazy, it even causes the form to be all lagged out.

This is one of my Dlookup:
DLookUp("Descricao","Unificada","Formula=" & [FormulaBox])

I don't understand why is my Dlookup returning error when not finding value instead of returning Null.

I've tried: Nz(DLookup(...)), IsNumeric(DLookup(...)), IsNumeric(FormatNumber(DLookup(...))), DLookup(...) >0. Basically, once the inputed value in [FormulaBox] is not on my table, it'll just return an error regardless of the functions I try to wrap around it.

Help please!!
Go to the top of the page
 
GroverParkGeorge
post Jan 20 2020, 09:42 AM
Post#2


UA Admin
Posts: 36,782
Joined: 20-June 02
From: Newcastle, WA


Welcome to UtterAccess.

This is expected behavior.

Null means "There is nothing here"; it is the absence of any value at all. it means that there may, or may not, be a value, but either way we don't know what it is, if it does exist. And we don't know if it does or does not exist.

Null has a specific purpose in Relational Databases, like MS Access. Here's an excellent starter article.

Domain functions, like DLookup, can't work with Nulls because they depend on comparing a value. Because Null means "there is no value", you can't compare Null to anything. It's sort of like asking "Is this blue?" when "this" could refer to anything or nothing. There is no way to identify a color for "this" because there is nothing there to have a color.

In this case, therefore, you need to start with the assumption that there may, or may not, be a value for your DLookup() to use.

The way to do that would be with the Iif() function, something like this:

Iif([FomulaBox] Is Null, "", DLookUp("Descricao","Unificada","Formula=" & [FormulaBox]))

Where you test for Null in the control, and return a Zero Length String if it is, otherwise perform the DLookup.
This post has been edited by GroverParkGeorge: Jan 20 2020, 10:02 AM

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
marcuscatan
post Jan 20 2020, 10:44 AM
Post#3



Posts: 15
Joined: 20-January 20



Thank you very much for your answer, I'm amused by how fast you replied, and thank you very much for the intro, very interesting.


But my code actually is exactly as you wrote (sorry I didn't input it entirely). But I find a problem not when [FormulaBox] is empty, I find a problem when the input there does not exist on my table.

Let's say my table of formulas is:

A
ZZ32A52
ZZ23A31
ZZ21ABE4D

(this follows absolutely no pattern, our formulas have letters and numbers and those are completely random).

I then use DLookup to check from that formula, what's the formula name, it's productivity target, etc.

But when somebody types in a formula that is not on my formula list, the glitch I talked about occurs (all DLookups return #Error and start blinking)
Go to the top of the page
 
GroverParkGeorge
post Jan 20 2020, 10:56 AM
Post#4


UA Admin
Posts: 36,782
Joined: 20-June 02
From: Newcastle, WA


"...when somebody types in a formula that is not on my formula list..."

Okay, so this is a Closed Domain of values?

By Closed Domain, I mean that there is a list (or Domain) of all possible or acceptable values. That list, or Domain, is relatively static; it doesn't change. One excellent example of that would be days of the week. There are seven. It will not grow to eight or shrink to six.

When you have a Closed Domain, a much more user friendly control is a combo box, rather than a free form text box.

You can create a Lookup table of values (here that would be your formulas) and use that as the row source for the combo box. Instead of making users remember and enter those formulas, they can simply pick the one they want from that combo box. No more errors in that regard.

Furthermore, because combo boxes support multiple columns, you can add additional columns that contain the values you want to lookup, in this case, that would be "Descricao" and you can then display that by referencing the column which contains it in the combo box.

BTW. I suspect that "formulas" may be slightly different in English from what you mean here. In English, a formula refers to something like "X + Y =Z". That doesn't look like the values you give as examples. They look more like what we would refer to as "codes". Is that a valid assumption on my part?

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
marcuscatan
post Jan 20 2020, 11:11 AM
Post#5



Posts: 15
Joined: 20-January 20



Thanks once again for your reply George,

I work at a plant that produces cosmetics, and for each bulk (each chemical formula, e.g. One random Nail Polish coloured bulk - that will be later bottled up) we have a code like "ZZ123AD231" or something. With no regards to the character length, letters or numbers. Everything can change in this code.

And so, it is not a closed domain, we have new formulas every week... frown.gif
This post has been edited by marcuscatan: Jan 20 2020, 11:13 AM
Go to the top of the page
 
GroverParkGeorge
post Jan 20 2020, 11:28 AM
Post#6


UA Admin
Posts: 36,782
Joined: 20-June 02
From: Newcastle, WA


Ah, I guess "formula" is appropriate in that case, too. It refers to the specific formula used to create that product. Language is fascinating and often ambiguous.

What you have then is an Open Domain. The list can grow, but anything NOT on the list is unacceptable.

In that case, the row source of the combo box can be based on a query on that field in the table where the codes are stored.

SQL
SELECT DISTINCT Formula, Descricao
FROM Unificada ORDER BY Formula

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
marcuscatan
post Jan 20 2020, 11:51 AM
Post#7



Posts: 15
Joined: 20-January 20



Worked perfectly, thank you so much!!!

Have a very nice day x)
Go to the top of the page
 
GroverParkGeorge
post Jan 20 2020, 11:52 AM
Post#8


UA Admin
Posts: 36,782
Joined: 20-June 02
From: Newcastle, WA


Glad to hear you solved the problem.

Continued success with your project.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    24th February 2020 - 06:01 PM