Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

IsNull and replace with "Text"

Status
Not open for further replies.
senthilmuruganv

senthilmuruganv

IS-IT--Management
Oct 2, 2012
5
US
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.

 
Sort by date Sort by votes
I'd use the Nz function:
SELECT ...,Nz(SampleDetails.RESULT_STATUS_1,"NA in DB") AS RESULT_STATUS
FROM ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
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?
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
So, you wanted this ?
ON Nz(SampleDetails.RESULT_STATUS_1,"NA in DB") = MasterCodes.CODE

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
Upvote 0 Downvote
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!

 
Upvote 0 Downvote
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
FAQ181-2886
 
Upvote 0 Downvote
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

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

JayRo71
  • Locked
  • Question
Saved Imoorts problem with file name
Replies
10
Views
801
Andrzejek
Andrzejek
MrStohler
  • Locked
  • Question
Beginner Q: How to separate data from Excel into related tables and maintain relationship? 1
Replies
5
Views
706
SkipVought
S
Anthony343242
  • Locked
  • Question
MS ACCESS UNION ALL with Attachment data type 1
2
Replies
29
Views
7K
Anthony343242
Anthony343242
kc27
  • Locked
  • Question
"Save Password" When Creating Links to Tables in Access 1
Replies
3
Views
432
kc27
kc27
jjMedrano
  • Locked
  • Question
UNION ALL with Attachment data type 1
Replies
8
Views
626
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top