• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 189
  • Last Modified:

Null Values in Access

I have joined two tables(table1 & employeeinfo) and when i enter the code below, i retrieve the desired results, but now i am trying to remove all rows that have a blank value for the employee name since those staff no longer work for the agency.  Employeename is retrieved from the employeeinfo table.  

I thought all i had to do is Where table1.lastuser Is Not Null AND EmployeeName Is NOT Null, but realized that means both those fields have to be Null to be removed.    

SELECT table1.LastUser, [LAST_NAME] & " " & [FIRST_NAME] AS EmployeeName, employeeinfo.REGION, employeeinfo.DEPTNAME, employeeinfo.JOBTITLE
FROM employeeinfo RIGHT JOIN table1 ON employeeinfo.LOGNAME=table1.LastUser
WHERE (((table1.LastUser) Is Not Null));

I have attached what the query looks like now.  The highlighted fields are the only ones i want to retain.
Building.xls
0
jsawicki
Asked:
jsawicki
  • 3
1 Solution
 
hnasrCommented:
Try INNER JOIN
0
 
hnasrCommented:
SELECT table1.LastUser, [LAST_NAME] & " " & [FIRST_NAME] AS EmployeeName, employeeinfo.REGION, employeeinfo.DEPTNAME, employeeinfo.JOBTITLE
FROM employeeinfo INNER JOIN table1 ON employeeinfo.LOGNAME=table1.LastUser
0
 
Gustav BrockCIOCommented:
You can use:

IIf(Trim([LAST_NAME] & " " & [FIRST_NAME]) = "", Null, Trim([LAST_NAME] & " " & [FIRST_NAME])) AS EmployeeName,

Then you filter for EmployeeName Is Null.

/gustav
0
 
jsawickiAuthor Commented:
Funny how simple that was, thanks.
0
 
hnasrCommented:
Welcome!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now