If BLANK (NULL) in a SELECT statement

kknb4591

New member
Local time
Today, 03:31
Joined
Aug 25, 2011
Messages
6
Hi,

I have the below code from the SQL view of access:

SELECT
tblDealsTeamreport.[CIBR Deal ID],
tblSalesTeamreport.[Team Member Name],
tblDealsTeamreport.[Associate Name],
tblNamesCIBRtoSF.[SF Name]
FROM
(tblDealsTeamreport
INNER JOIN tblSalesTeamreport ON tblDealsTeamreport.[CIBR Deal ID] = tblSalesTeamreport.[CIBR Deal ID])
LEFT JOIN tblNamesCIBRtoSF ON tblDealsTeamreport.[Associate Name] = tblNamesCIBRtoSF.[CIBR Name]

What I'm getting though is a few blank (NULL) results in the [SF Name] column and this is because those names are not contained in tblNamesCIBRtoSF. What sort of IF statement could I write in order for when 'SF Name' is blank it selects the [Associate Name] instead and puts that in the [SF Name] column?

Any help or suggestions would be greatly appreciated.
 
Code:
SELECT 
tblDealsTeamreport.[CIBR Deal ID], 
tblSalesTeamreport.[Team Member Name], 
tblDealsTeamreport.[Associate Name], 
IIF(IsNull(tblNamesCIBRtoSF.[SF Name]), tblDealsTeamreport.[Associate Name], tblNamesCIBRtoSF.[SF Name]) AS modifiedSFNAME 
FROM
(tblDealsTeamreport 
INNER JOIN tblSalesTeamreport ON tblDealsTeamreport.[CIBR Deal ID] = tblSalesTeamreport.[CIBR Deal ID])
LEFT JOIN tblNamesCIBRtoSF ON tblDealsTeamreport.[Associate Name] = tblNamesCIBRtoSF.[CIBR Name]
 
An IIf() Statement should provide you what you are looking for. The Syntax for that is as follows:

IIf( {Condition to test}, {Value if TRUE}, {Value if FALSE} )

You should be able to take it from here

NOTE: It looks like another user was posting around the same time and provided a complete detailed answer.
 
Last edited:
Nz() would be a more succint option:
Code:
Nz(tblNamesCIBRtoSF.[SF Name], tblDealsTeamreport.[Associate Name]) As SomeAlias
 
Thanks all I ended up using:

SELECT
tblDealsTeamreport.[CIBR Deal ID],
tblSalesTeamreport.[Team Member Name],
tblDealsTeamreport.[Associate Name],
IIf(IsNull(tblNamesCIBRtoSF.[SF Name]),tblDealsTeamreport.[Associate Name],tblNamesCIBRtoSF.[SF Name]) AS [New Associate Name], tblSalesTeamreport.[Team Role]

Spun gold!
 

Users who are viewing this thread

Back
Top Bottom