How to Replace Blanks using SQL

Question

Hi, I created a query, but need it to look at one column and replace any blanks in the column with a specific message "Reviewer not Selected".

Here is my SQL:

SELECT [tbl_Case Tracker End Table].[Case Status], [tbl_Case Tracker End Table].[Date Received], [tbl_Case Tracker End Table].[Cx/Business Name], [tbl_Case Tracker End Table].[Cx unique identifier (CIF/MDM#)], [tbl_Case Tracker End Table].[Focal/Related], [tbl_Case Tracker End Table].[Referring Group], [tbl_Case Tracker End Table].[Case Type], [tbl_Case Tracker End Table].[Trigger/Source of Escalation], [tbl_Case Tracker End Table].[CB&DC DH Team Assigned], [tbl_Case Tracker End Table].[CB&DC DH Reviewer], [tbl_Case Tracker End Table].[Case is Pending With], [tbl_Case Tracker End Table].Notes, [tbl_Case Tracker End Table].Investigator, [tbl_Case Tracker End Table].[Line of Business]

FROM [tbl_Case Tracker End Table]

WHERE ((([tbl_Case Tracker End Table].[Case Status])="open" Or ([tbl_Case Tracker End Table].[Case Status])="unassigned") AND (([tbl_Case Tracker End Table].[Focal/Related])="Focal Party"));

I need it to check [tbl_Case Tracker End Table].[CB&DC DH Reviewer] and replace the blanks with the message.

I tried the nz, but couldn't get it to work.

Thanks

Answer 1

Add an expression in your query... something like:

Expr1: IIf(IsNull([CB&DC DH Reviewer]),"MyMessage",[CB&DC DH Reviewer])

Then use Expr1 in your report instead of the field CB&DC DH Reviewer

and ... probably change the name 'Expr1' to something more meaningful

Answer 2

Your query design has a criteria row. Add Null under the field so you are only retrieving records where the updating field is null. Then add the update to and hope it isn’t a numeric field.

Hi Duane,

Thanks for your response. Apologies, I may not have been clear. I do not want to change it permanently, as eventually this field should be populated, I just want the report to show a message vs not showing anything. 

So I do not want the source data in the table to be changed, I just want the report to indicated the information was not added (this is the ultimate purpose of the report, to show which cases haven't been assigned to a specific reviewer.

Make sense? Hope I helped make it more clear.

thank you,

DV

Answer 3

Your query design has a criteria row. Add Null under the field so you are only retrieving records where the updating field is null. Then add the update to and hope it isn’t a numeric field.

Answer 4

You would need to add Null to the criteria under the field you want to replace and enter the value you want in the same column in the update to.

Hi Duane, thanks for the reply.

Sorry, but how?

Answer 5

You would need to add Null to the criteria under the field you want to replace and enter the value you want in the same column in the update to.