Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 397
  • Last Modified:

Problem with DLookup/null records in Access Project

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
rfiddler
Asked:
rfiddler
2 Solutions
 
Benjamin_LukCommented:
change :
sResult = DLookup(sExpr, "Network", "NetworkID=18436")
To:
sResult = NZ(DLookup(sExpr, "Network", "NetworkID=18436"),"")

Regards

Ben
0
 
morpheus30Commented:
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
 
rfiddlerAuthor Commented:
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now