Solved

Problem with DLookup/null records in Access Project

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

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

770 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