ACCESS FEED: RE: [MS_AccessPros] Field missing from expression

Senin, 02 Juni 2014

RE: [MS_AccessPros] Field missing from expression

Thanks Graham,

I have separate fields for [mLastName] and [fLastName] to accommodate that possibility. I have not built it that far yet.

Your idea fixed my first problem.

Thanks

Robin

At 3/06/2014 08:47 AM, you wrote:

Hi Robin

Are you sure [mLastName] is actually Null, and not a ZLS (zero-length string)?

And what about a situation where there is a male resident, but with a different last name – e.g. John Jones and Betty Brown?

You might like to try this:

SELECT …
[fFirstName] & IIf( [fLastName]=[mLastName], "", " " & [fLastName] ) AS fResident
… FROM Telephone;

Best wishes,
Graham

From: MS_Access_Professionals@yahoogroups.com [ mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Tuesday, 3 June 2014 08:34
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Field missing from expression

I have the SQL below to d eliver "Mary Smith" as the female resident
when no male is shown as resident in the record and "Betty" to be
shown when Betty has a partner.

For some reason that I cannot understand the male residents are
exclude as expected but the female residents record [fFirstname] only
is included.

What have I missed?

Many thanks,

Robin Chapple

SELECT Telephone.ID, Telephone.Unit, Telephone.fFirstName,
Telephone.fLastName, Telephone.mFirstName, Telephone.mLastName,
Telephone.Phone, IIf([mLastName] Is Null,[fFirstName] & " " &
[fLastName],[fFirstName]) AS fResident
FROM Telephone;