?
Solved

Null Values in Access

Posted on 2012-03-21
5
Medium Priority
?
187 Views
Last Modified: 2012-03-22
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
Comment
Question by:jsawicki
  • 3
5 Comments
 
LVL 31

Expert Comment

by:hnasr
ID: 37750887
Try INNER JOIN
0
 
LVL 31

Accepted Solution

by:
hnasr earned 1600 total points
ID: 37750898
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
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 37751296
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
 

Author Closing Comment

by:jsawicki
ID: 37754306
Funny how simple that was, thanks.
0
 
LVL 31

Expert Comment

by:hnasr
ID: 37754626
Welcome!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question