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
jsawickiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hamed NasrRetired IT ProfessionalCommented:
Try INNER JOIN
Hamed NasrRetired IT ProfessionalCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
jsawickiAuthor Commented:
Funny how simple that was, thanks.
Hamed NasrRetired IT ProfessionalCommented:
Welcome!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.