IsNull and replace with "Text"

[senthilmuruganv]

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.

 

[PHV]

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

 

[senthilmuruganv]

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?

 

[Golom]

I think what PHV meant is that

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.

 

[PHV]

So, you wanted this ?
ON Nz(SampleDetails.RESULT_STATUS_1,"NA in DB") = MasterCodes.CODE

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[senthilmuruganv]

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!

 

[PHV]

What about this ?

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

 

[senthilmuruganv]

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