×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

IsNull and replace with "Text"

thread700-1694816

IsNull and replace with "Text"

IsNull and replace with "Text"

(OP)
Hi,

I have a following query and it is working fine. However, I want to introduce check for the null value and replace with null value column with "NA in DB"

The query is:

SELECT DISTINCT SampleDetails.UNIT_ID, SampleDetails.COLL_DATE, TestKitReagents.TEST_KIT_ID, TestBatchDetails.TEST_ID, MasterCodes.CODE_DESC, TestBatchDetails.TB_ID, SampleDetails.ABSORBANCE, MasterCodes.CODE_TYPE
FROM (SampleDetails INNER JOIN (TestKitReagents INNER JOIN (ValidReagents INNER JOIN TestBatchDetails ON ValidReagents.TEST_ID = TestBatchDetails.TEST_ID) ON TestKitReagents.TEST_KIT_ID = TestBatchDetails.TEST_KIT_ID) ON (SampleDetails.TEST_ID = TestBatchDetails.TEST_ID) AND (SampleDetails.TB_ID = TestBatchDetails.TB_ID)) INNER JOIN MasterCodes ON SampleDetails.RESULT_STATUS_1 = MasterCodes.CODE
WHERE (((SampleDetails.UNIT_ID)=[Forms]![SampleDetails_Form].[UNIT_ID]) AND ((SampleDetails.COLL_DATE)=[Forms]![SampleDetails_Form].[COLL_DATE]) AND ((MasterCodes.CODE_TYPE)="RESUL"));


In the above query, I want to check SampleDetails.RESULT_STATUS_1 is null then I have to result_status as "NA in DB". I used IIF function and Is null, but couldn't succeed.

Please help me to solve this problem.

Thank you in advance.

RE: IsNull and replace with "Text"

(OP)
Thanks PH!

I am not using SampleDetails.RESULT_STATUS_1 in my select class. Just I am selecting SELECT DISTINCT SampleDetails.UNIT_ID, SampleDetails.COLL_DATE, TestKitReagents.TEST_KIT_ID, TestBatchDetails.TEST_ID, MasterCodes.CODE_DESC, TestBatchDetails.TB_ID, SampleDetails.ABSORBANCE, MasterCodes.CODE_TYPE

Again the query is:

SELECT DISTINCT
SampleDetails.UNIT_ID, SampleDetails.COLL_DATE, TestKitReagents.TEST_KIT_ID, TestBatchDetails.TEST_ID, MasterCodes.CODE_DESC, TestBatchDetails.TB_ID, SampleDetails.ABSORBANCE, MasterCodes.CODE_TYPE

FROM

(SampleDetails INNER JOIN (TestKitReagents INNER JOIN (ValidReagents INNER JOIN TestBatchDetails ON ValidReagents.TEST_ID = TestBatchDetails.TEST_ID) ON TestKitReagents.TEST_KIT_ID = TestBatchDetails.TEST_KIT_ID) ON (SampleDetails.TEST_ID = TestBatchDetails.TEST_ID) AND (SampleDetails.TB_ID = TestBatchDetails.TB_ID)) INNER JOIN MasterCodes ON SampleDetails.RESULT_STATUS_1 = MasterCodes.CODE

WHERE (((SampleDetails.UNIT_ID)=[Forms]![SampleDetails_Form].[UNIT_ID]) AND ((SampleDetails.COLL_DATE)=[Forms]![SampleDetails_Form].[COLL_DATE]) AND ((MasterCodes.CODE_TYPE)="RESUL"));



Can you please give an idea to proceed this?

RE: IsNull and replace with "Text"

I think what PHV meant is that

CODE

Nz(SomeField,"NA in DB") AS [Some Alias]
Will return 'NA or DB' for any field if that field IS NULL. The only caution is that you should not do this if "SomeField" has a numeric or date data type. Numerics and dates cannot store text strings.

RE: IsNull and replace with "Text"

(OP)
Many Thanks PH, Golom: Great help!

I tried with your suggestion, but it didn't work.

The query retrieves the result based on MasterCodes.CODE_TYPE)="RESUL" and this (MasterCodes.CODE_TYPE is checked with SampleDetails.RESULT_STATUS_1. The table MasterCodes is a code table. It retrieves the information perfectly. However, if result_status_1 is null there is no corresponding entry in master.code.code_type. Uses is not interested to add corresponding code for Null column.

My problem is, if result_status_1 is null in my sample table it has to retrieve the corresponding record with Description as " Not in DB"



Your help will be highly appreciated.

Thanks!

RE: IsNull and replace with "Text"

What about this ?

CODE

SELECT S.UNIT_ID, S.COLL_DATE, K.TEST_KIT_ID, B.TEST_ID, Nz(M.CODE_DESC,'Not in DB') AS CodeDesc, B.TB_ID, S.ABSORBANCE, M.CODE_TYPE
  FROM (((SampleDetails S
 INNER JOIN TestBatchDetails B ON S.TEST_ID = B.TEST_ID AND S.TB_ID = B.TB_ID)
 INNER JOIN TestKitReagents K ON B.TEST_KIT_ID = K.TEST_KIT_ID)
 INNER JOIN ValidReagents V ON B.TEST_ID = V.TEST_ID)
  LEFT JOIN MasterCodes M ON S.RESULT_STATUS_1 = M.CODE
 WHERE S.UNIT_ID=[Forms]![SampleDetails_Form].[UNIT_ID]
   AND S.COLL_DATE=[Forms]![SampleDetails_Form].[COLL_DATE]
   AND Nz(M.CODE_TYPE,'RESUL')='RESUL'

Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: IsNull and replace with "Text"

(OP)
Thanks again PHV!

But the code is not working appropriately.

Description column is not retrieved with proper description, if the code is matched.

Thanks again

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close