Solved

Problem with DLookup/null records in Access Project

Posted on 2003-12-02
3
364 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
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

896 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

12 Experts available now in Live!

Get 1:1 Help Now