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

Posted on 2004-11-27
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
    LVL 77

    Accepted Solution

    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

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

    LVL 77

    Expert Comment

    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

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Suggested Solutions

    Introduction If you are not already aware of what you could use a table with sequential integer values for in SQL, you can read Delimited String Parsing in SQL Server 2005 or later ( by BrandonGalderisi (h…
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now