Getting an alternate value by query

[Amer]

I want to create a query that combines employees contact details and in the
Email field I should be able to get the employees's department email if the
employee does not have an email of his own.

Actually I tried that using IIF and IsNull, but it doesn't work...

Any suggestions?

 

[pietlinden]

I want to create a query that combines employees contact details and in the
Email field I should be able to get the employees's department email if the
employee does not have an email of his own.

Actually I tried that using IIF and IsNull, but it doesn't work...

Any suggestions?

table structures?

given a table, Employee with the following fields:
DeptEmail, PersonalEMail

IIF(IsNull([PersonalEmail]),[DeptEmail],[PersonalEMail])

 

[Tom Wickerath]

Hi Amer,

It would be helpful if you can copy the SQL (Structured Query Language)
statement, and paste it into a reply, so that others can see exactly what you
have tried. Also, instead of just stating "it doesn't work", tell us exactly
what you see as the result.

To view the SQL statement, click on View | SQL View, when in query design
view (Access 2003 and lower).


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Amer]

Thank you for the tips.

Here's the SQL for you:

---------------------------------------------------------------------------------------
SELECT tabGroups.GroupRef, tabGroups.Rating, tabGroups.Part,
tabGroups.InstructionDays, tabGroups.OnHours, tabGroups.SessionDuration,
tabGroups.StartingDate, tabGrouping.FileNo, tabGrouping.EnglishName,
tabGrouping.ArabicName, qryEmployeesDetails.DepartmentName,
qryEmployeesDetails.CompanyName, qryEmployeesDetails.Email, Contact.Email
FROM ((tabGrouping INNER JOIN (tabGroups INNER JOIN tabApplications ON
tabGroups.CourseRef = tabApplications.CourseRef) ON (tabGroups.GroupRef =
tabGrouping.GroupRef) AND (tabGrouping.FileNo = tabApplications.FileNo))
INNER JOIN tabContactInfo AS Contact ON tabApplications.ApprovedBy =
Contact.FileNo) INNER JOIN qryEmployeesDetails ON tabGrouping.FileNo =
qryEmployeesDetails.FileNo
WHERE
(((qryEmployeesDetails.Email)=IIf(IsNull([qryEmployeesDetails.Email]),[Contact.Email],[qryEmployeesDetails.Email])));

 

[Tom Wickerath]

Hi Amer,

You might want to verify that the Email field does not contain zero length
strings (ZLS), in place of null. Both situations would have the same visual
appearance. In table design view, does the Email field include a setting of
Yes for the "Allow Zero Length" property? To read more about this issue, see
this article:

Fields: Allow Zero Length
http://allenbrowne.com/bug-09.html

I can't say that I have ever seen the use of an IIF statement in the
criteria of a query. Perhaps this will work, but I just don't know for sure.
However, is there a reason that you cannot add this IIF statement instead to
the "qryEmployeesDetails" query? This should allow you to eliminate the
criteria in your existing query. Something like this, in the
"qryEmployeesDetails" query design view:

EMailAddy: IIF(IsNull(),[Contact.Email],[Email])

Then include the EMailAddy field in your new query.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Amer]

The ZLS criteria is already set to Yes.

Actually, I need to get the alternate email for this specific query only. I
want to keep the original "qryEmployeesDetails" for other purposes, so I
don't need to use the alternate email for it.

Hi Amer,

You might want to verify that the Email field does not contain zero length
strings (ZLS), in place of null. Both situations would have the same visual
appearance. In table design view, does the Email field include a setting of
Yes for the "Allow Zero Length" property? To read more about this issue, see
this article:

Fields: Allow Zero Length
http://allenbrowne.com/bug-09.html

I can't say that I have ever seen the use of an IIF statement in the
criteria of a query. Perhaps this will work, but I just don't know for sure.
However, is there a reason that you cannot add this IIF statement instead to
the "qryEmployeesDetails" query? This should allow you to eliminate the
criteria in your existing query. Something like this, in the
"qryEmployeesDetails" query design view:

EMailAddy: IIF(IsNull(),[Contact.Email],[Email])

Then include the EMailAddy field in your new query.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

[QUOTE="Amer"]
Thank you for the tips.

Here's the SQL for you:

---------------------------------------------------------------------------------------
SELECT tabGroups.GroupRef, tabGroups.Rating, tabGroups.Part,
tabGroups.InstructionDays, tabGroups.OnHours, tabGroups.SessionDuration,
tabGroups.StartingDate, tabGrouping.FileNo, tabGrouping.EnglishName,
tabGrouping.ArabicName, qryEmployeesDetails.DepartmentName,
qryEmployeesDetails.CompanyName, qryEmployeesDetails.Email, Contact.Email
FROM ((tabGrouping INNER JOIN (tabGroups INNER JOIN tabApplications ON
tabGroups.CourseRef = tabApplications.CourseRef) ON (tabGroups.GroupRef =
tabGrouping.GroupRef) AND (tabGrouping.FileNo = tabApplications.FileNo))
INNER JOIN tabContactInfo AS Contact ON tabApplications.ApprovedBy =
Contact.FileNo) INNER JOIN qryEmployeesDetails ON tabGrouping.FileNo =
qryEmployeesDetails.FileNo
WHERE
(((qryEmployeesDetails.Email)=IIf(IsNull([qryEmployeesDetails.Email]),[Contact.Email],[qryEmployeesDetails.Email])));
--------------------------------------------------------------------------

Now after adding the criteria that checks of the email is null and replacing
that with another email, the query filters the data and shows only those
employees who have their own emails...![/QUOTE][/QUOTE]

 

[Tom Wickerath]

Hi Amer,

The ZLS criteria is already set to Yes.

In that case, you may need to test for ZLS instead of testing for IsNull.
Perhaps something like this:

WHERE
(((qryEmployeesDetails.Email)=IIf(Len([qryEmployeesDetails.Email] &
"")=0,[Contact.Email],[qryEmployeesDetails.Email])));

Here, we use the Len function to test for the length of the Email field
concatenated with a zero length string. That way, if the Email field is truly
null, then a null & "" will be a ZLS. On the other hand, if the Email field
does include a ZLS already (since the Allow ZLS property was set to Yes),
then the result will still evaluate to a ZLS, with length = 0.

An alternative would be to set the Allow zero length property to No (Allen's
recommendation), and then run an update query to update any existing ZLS
values to nulls. That way, you could continue to use your existing IsNull
test. I suspect that the reason your WHERE clause does not appear to be
working right now is that none of the records include a null EMail value;
they either have an e-mail address, or they have a zero length string, which
will appear visually the same to you.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[John W. Vinson]

WHERE
(((qryEmployeesDetails.Email)=IIf(Len([qryEmployeesDetails.Email] &
"")=0,[Contact.Email],[qryEmployeesDetails.Email])));

I wonder if the bracketing might also be part of the problem? Rather than
[qryEmployeesDetails.Email] - brackets around the entire expression - I
would suggest [qryEmployeesDetails]. - brackets around each object's
name.

 

[Amer]

That did not work... unfortunately.

The query is still showing those records which have their own email and not
showing those without...

Hi Amer,

The ZLS criteria is already set to Yes.

In that case, you may need to test for ZLS instead of testing for IsNull.
Perhaps something like this:

WHERE
(((qryEmployeesDetails.Email)=IIf(Len([qryEmployeesDetails.Email] &
"")=0,[Contact.Email],[qryEmployeesDetails.Email])));

Here, we use the Len function to test for the length of the Email field
concatenated with a zero length string. That way, if the Email field is truly
null, then a null & "" will be a ZLS. On the other hand, if the Email field
does include a ZLS already (since the Allow ZLS property was set to Yes),
then the result will still evaluate to a ZLS, with length = 0.

An alternative would be to set the Allow zero length property to No (Allen's
recommendation), and then run an update query to update any existing ZLS
values to nulls. That way, you could continue to use your existing IsNull
test. I suspect that the reason your WHERE clause does not appear to be
working right now is that none of the records include a null EMail value;
they either have an e-mail address, or they have a zero length string, which
will appear visually the same to you.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

The ZLS criteria is already set to Yes.

Actually, I need to get the alternate email for this specific query only. I
want to keep the original "qryEmployeesDetails" for other purposes, so I
don't need to use the alternate email for it.

 

[Amer]

That did not work... unfortunately.

The query is still showing those records which have their own email and not
showing those without...

WHERE
(((qryEmployeesDetails.Email)=IIf(Len([qryEmployeesDetails.Email] &
"")=0,[Contact.Email],[qryEmployeesDetails.Email])));

I wonder if the bracketing might also be part of the problem? Rather than
[qryEmployeesDetails.Email] - brackets around the entire expression - I
would suggest [qryEmployeesDetails]. - brackets around each object's
name.[/QUOTE]

 

[John W. Vinson]

I want to create a query that combines employees contact details and in the
Email field I should be able to get the employees's department email if the
employee does not have an email of his own.

Actually I tried that using IIF and IsNull, but it doesn't work...

Any suggestions?

Don't include the email field in the query criteria AT ALL, then; instead just
create a calculated field in the query to return the department email if the
employee email is null or zero-length. NULL is not equal to anything, even
another NULL, so the criterion will exclude all records where the field is
null. Try

SELECT tabGroups.GroupRef, tabGroups.Rating, tabGroups.Part,
tabGroups.InstructionDays, tabGroups.OnHours, tabGroups.SessionDuration,
tabGroups.StartingDate, tabGrouping.FileNo, tabGrouping.EnglishName,
tabGrouping.ArabicName, qryEmployeesDetails.DepartmentName,
qryEmployeesDetails.CompanyName,
IIF(Len(([qryEmployeeDetails]. & "") = 0, [Contact].[Email],
[qryEmployeeDetails].[Email]) AS UseThisEmail
FROM ((tabGrouping INNER JOIN (tabGroups INNER JOIN tabApplications ON
tabGroups.CourseRef = tabApplications.CourseRef) ON (tabGroups.GroupRef =
tabGrouping.GroupRef) AND (tabGrouping.FileNo = tabApplications.FileNo))
INNER JOIN tabContactInfo AS Contact ON tabApplications.ApprovedBy =
Contact.FileNo) INNER JOIN qryEmployeesDetails ON tabGrouping.FileNo =
qryEmployeesDetails.FileNo;

 

[Amer]

It worked after I added the following calculated column:

Expr1: IIf(Len([qryEmployeesDetails].(e-mail address removed)

 

[John W. Vinson]

It worked after I added the following calculated column:

Expr1: IIf(Len([qryEmployeesDetails].(e-mail address removed)

 

[Amer]

I solved this by basing a form on this query and selcting Yes to the Is
Hyperlink property in the format tab.

Now everything seems allright. Thnak you for your very valuable assistance.

It worked after I added the following calculated column:

Expr1: IIf(Len([qryEmployeesDetails].(e-mail address removed)