Null recognition

[Moxioron]

Hello all.

I have two tables that I have joined and I am having an issue if the Reg E Dispute Data table CompCode column is blank. The Close Code in tbl_ADH Close Codes table is joined to the CompCode in the Reg E Dispute Data table. I want the description in the Code Description column to reflect if the Closed Code and the CompCode fields are blank. How does one accomplish that? I hope that made sense.

Here is my sql. Thanks for your help.

SELECT [Reg E Dispute Data].CaseID, [Reg E Dispute Data].DateOpened, [Reg E Dispute Data].ClaimAmount, [Reg E Dispute Data].Type, [Reg E Dispute Data].PaymentChannel, [Reg E Dispute Data].DateClosed, [tbl_ADH Close Codes].[Code Description]
FROM [Reg E Dispute Data] LEFT JOIN [tbl_ADH Close Codes] ON [Reg E Dispute Data].CompCode = [tbl_ADH Close Codes].[Close Code]
WHERE ((([Reg E Dispute Data].DateOpened) Between [Start Date] And [End Date]) AND (([Reg E Dispute Data].Type)="DS" Or ([Reg E Dispute Data].Type)="FR") AND (([Reg E Dispute Data].PaymentChannel) Like [Enter Payment Channel] & "*"));

 

[Joe4]

I am not quite sure I understand. If you have NULLs or blanks in the fields you are trying to join on, those records will not be returned by your query, as you have written it.

Can you provide a simple example?
Just provide the data from the fields from one of these records you are trying to describe (one record from each table), and explain your expected result.

 

[Moxioron]

The tbl_ADH Close Codes table has two columns. One is Close Code and one is Code Description. I have the Close Code column of that table joined with the CompCode column of the Reg E Dispute Data table.

In the tbl_ADH Close Codes table I have the list of close codes and the code description. In this table I have a blank in the Close Code column and a description of 'Still Open'.

If the CompCode column in the Reg E Dispute Data table is empty, I want my query to return the 'Still Open' description.

I can't seem to get it to work.

Does that help? Thanks.

 

[Joe4]

<HR style="BACKGROUND-COLOR: #ffffff; COLOR: #ffffff" SIZE=1>

I am not quite sure I understand. If you have NULLs or blanks in the fields you are trying to join on, those records will not be returned by your query, as you have written it.

Actually, I misspoke here earlier. I missed that you are using a LEFT JOIN.

Anyway, I think you might be looking for the NZ function. This tells you what to return in the event of a null entry, i.e.

NZ([Reg E Dispute Data].CompCode,"Still Open")