Learn how to a build a cloud-first strategyRegister Now


Dlookup - if exists, do this, if not - do that!

Posted on 2004-11-27
Medium Priority
Last Modified: 2012-05-05
This Dlookup looks at a table called dbo_engineers, and if what the use has entered exists returns the number and does some other stuff:

If Nz(DLookup("[Engineer number]", "dbo_Engineers", "[Engineer number] = " & Me!Engineer), "") <> "" Then

'then do this

I would like a message box to come up and say "does not exist" if the lookup above returns no engineer number.

Question by:Lapchien
  • 2
LVL 77

Accepted Solution

peter57r earned 1000 total points
ID: 12686004
Hello Lapchien,

This assumes 'Engineer number' is a number (not text).

If isnull(DLookup("[Engineer number]", "dbo_Engineers", "[Engineer number] = " & Me!Engineer) Then
    msgbox "Engineer number " & me!engineer & "  does not exist"
'do nothing
end if  


Author Comment

ID: 12686341
Thanks Pete.  Incidentally, what does the 'Nz' do?

LVL 77

Expert Comment

ID: 12689080
The NZ function tests the result of an expression for a Null value and if it finds Null it reurns the value following the comma.
Nz(MYVariable, 'Empty')   returns the text  'Empty'  if MyVariable evaluates to Null.

You can alse use :
nz(<expression>)  without the second parameter.  
In this case if  a non-null <expression> would be a number then NZ returns 0 (zero) ; if the non-null value would be text then NZ returns "".


Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question