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.
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"
SELECT ...,Nz(SampleDetails.RESULT_STATUS_1,"NA in DB") AS RESULT_STATUS
FROM ...
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"
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"
CODE
RE: IsNull and replace with "Text"
ON Nz(SampleDetails.RESULT_STATUS_1,"NA in DB") = MasterCodes.CODE
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"
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"
CODE
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"
But the code is not working appropriately.
Description column is not retrieved with proper description, if the code is matched.
Thanks again