Need Help With DLookup in Query: Handling Null (#Error) With Numeric Fields

[larrytxeast]

This is my 1st post ever here, thank you.

I'm using Access 2010. I have the following "base" DLookup statement that works except for null situations:

BadgeNumber1: DLookUp("[BadgeNumber]","tblChefs","[autoid] =" & [FirstChoiceChef])

If there is nothing to look up, it returns #Error. I got the bright idea of making a modified statement to handle null situations, it looks like this:

IIf(IsNull(DLookUp("[BadgeNumber]","tblChefs","[autoid] =" & [FirstChoiceChef])),"NothingToSee",DLookUp("[BadgeNumber]","tblChefs","[autoid] =" & [FirstChoiceChef]))

(it uses this as its foundation: IIf(IsNull(x), "Oops", x) where x is the previous "base" DLookup statement)

There are no syntax errors, however it still returns #Error, and I'm guessing it's because "BadgeNumber" is a numeric field. Blank values that produce this error, they don't have a default value of 0, they are just blank.

Tips?

[June7]

If DLookup does not find a match, it should just return null.

What is the 'null situation' - is it possible FirstChoiceChef could be null?

[larrytxeast]

If DLookup does not find a match, it should just return null.

What is the 'null situation' - is it possible FirstChoiceChef could be null?

Yes, FirstChoiceChef could be null, and when I looked back at the database in such #Error cases it was in fact on occasions when this value was null (or blank etc).

Maybe I'm instead supposed to have the IIf(IsNull) portion instead check to see if FirstChoiceChef is null instead of that entire equation?

[June7]

Yes, that would be the case. Handle the possible null. Give the expression something else to process.

[larrytxeast]

Yes, that would be the case. Handle the possible null. Give the expression something else to process.

Yes, thanks. I did and it fixed it, the expression looks like so (excluding the custom variable name):

IIf(IsNull([FirstChoiceChef]),"NothingChosen", DLookUp("[BadgeNumber]","tblChefs","[autoid] =" & [FirstChoiceChef]))

[June7]

There may be better options than using DLookup because domain aggregate functions can be slow performers in query.

So why do you even need the badge number in the query?

Why not just include tblChefs in the query?

[larrytxeast]

There may be better options than using DLookup because domain aggregate functions can be slow performers in query.

So why do you even need the badge number in the query?

Why not just include tblChefs in the query?

Care to elaborate on the better options?

I have done setups where I included tblChefs in the query, where it was looking up the chef's name with the ChefID field in the "base" table (so could see their actual name), in this case however it got confusing because there are 3 fields--FirstChoice, SecondChoice and ThirdChoice all which have the autoid value from the tblChef plugged instead of the name (as should be the case). I kept running into errors and gave up doing it that way.

In this one other forum, (access-programmers.co.uk), there was an "introduce yourself" forum or whatever, and I was able to elaborate a bit there on my background. I'm not a hard-core programmer, although I do a fair amount of code. Most of all, I hadn't done databases in several years (2010 was the previous occasion) so I had become quite a bit "stale" in remembering basic things. A previous co-worker called me up needing help with an Access database so basically I've "rebooted" but with many cobwebs still in my head from being "stale" (although after a week of this much of this has cleared up, not all however). So I am not as "fresh" as others, and perhaps not as fluent regardless. I'm a clear step above a clerical/administrative person, however. (Besides that I do know some code, also notice the Redick and Leszynski naming conventions.) Maybe I'm an "advanced power user" or something of that caliber.

[June7]

Three 'choice' fields is not strictly normalized structure. Suppose you want to find all records with chef Jim, regardless of which choice field he is in - this means applying the same filter criteria to all 3 fields with OR operator.

To display the associated chef info for each 'choice' field, the query would have to pull in tblChefs 3 times, 1 each joined to each of the 'choice' fields.

[larrytxeast]

Three 'choice' fields is not strictly normalized structure. Suppose you want to find all records with chef Jim, regardless of which choice field he is in - this means applying the same filter criteria to all 3 fields with OR operator.

To display the associated chef info for each 'choice' field, the query would have to pull in tblChefs 3 times, 1 each joined to each of the 'choice' fields.

Oh dear, I think you're right. Fortunately, I don't think anyone is running that filter, no one needs to know that, but structurally speaking you're right. At this point I'm so deep in it would be a major undertaking (somewhat) to reconfigure it this way, but if I do somehow get the time I may well normalize this aspect. (And thanks for helping me remember what that term is called.)