Solved

Problem with DLookup/null records in Access Project

Posted on 2003-12-02
3
388 Views
Last Modified: 2012-08-13
I'm working on an Access Project using SQL Server 7 database.
I'm having a problem with the DLookup function.

Here is an example of my problem:

sExpr = "CASE WHEN [FirstName]=NULL THEN 'No First Name' ELSE [FirstName] END"
sResult = DLookup(sExpr, "Network", "NetworkID=18436")

If FirstName record is null, it returns:
  Error:
  Run-time error '94':
  Invalid use of Null

If FirstName record is not null:
  sResult = 'Rob'
-----------------
If I change the expression syntax to:
sExpr = "CASE WHEN [FirstName]<>NULL THEN [FirstName] ELSE 'No First Name' END"
sResult = DLookup(sExpr, "Network", "NetworkID=18436")

If FirstName record is null:
sResult = 'No First Name'

If FirstName record is not null:
sResult = NULL
** I need this to return the value of [FirstName] if it is not null! **

Can somebody tell me how to write this expression so that DLookup can use it and be able to handle null records?
Thanks!

~Rob
0
Comment
Question by:rfiddler
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 8

Accepted Solution

by:
Benjamin_Luk earned 50 total points
ID: 9863623
change :
sResult = DLookup(sExpr, "Network", "NetworkID=18436")
To:
sResult = NZ(DLookup(sExpr, "Network", "NetworkID=18436"),"")

Regards

Ben
0
 
LVL 5

Assisted Solution

by:morpheus30
morpheus30 earned 30 total points
ID: 9863767
BTW, the line:
sExpr = "CASE WHEN [FirstName]=NULL THEN 'No First Name' ELSE [FirstName] END"

should be:

sExpr = "CASE WHEN [FirstName] IS NULL THEN 'No First Name' ELSE [FirstName] END"

Then you can use DLookup as suggested by Benjamin_Luk.

My two cents...
0
 

Author Comment

by:rfiddler
ID: 9870756
Thanks Ben and Morpheus!
It's working now.

I'm increasing the points and rewarding Morpheus for the two cents as well.

Thanks again.

~Rob
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

635 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