jsawicki
asked on
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=table
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
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=table
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
Hamed Nasr
Try INNER JOIN
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
IIf(Trim([LAST_NAME] & " " & [FIRST_NAME]) = "", Null, Trim([LAST_NAME] & " " & [FIRST_NAME])) AS EmployeeName,
Then you filter for EmployeeName Is Null.
/gustav
ASKER
Funny how simple that was, thanks.
Welcome!