MS ACCESS 2002: How to DESIGN a semi-complex QUERY EXPRESSION FIELD???

[Alan Mailer]

Ok, my Access 2002 language writing skills are VERY rusty,. I would
know how to do what I need using SQL Server's "Coalesce' function, but
I don't have that available to me in the Access 2002 database I'm
currently programming.

....So could someone advise me how to add an Expression Field to a
Access 2002 Query that will reflect the following:

Imagine I want a field that derives from Fields in a table I've called
tblContacts. tblContacts has fields called FirstName, LastName and
CompanyName.

Here's what I want the Expression Field (which I'll call 'FullName')
to reflect:

1) If neither FirstName nor LastName are NULL, then FullName should
be the equivalent of LastName, FirstName (that's the LastName followed
by a comma followed by the FirstName.

2) If LastName is NULL, but FirstName is not NULL, then FullName
should be the equivalent of FirstName.

3) If both LastName and FirstName are NULL, and CompanyName is not
null, then FullName should be the equivalent of CompanyName.

So that's it. How would you 'design' this FullName field in Access'
Query Design view?

Thanks in advance.

[Bob Quintal]

Alan Mailer <cl**********@earthlink.netwrote in
news:49********************************@4ax.com:

Ok, my Access 2002 language writing skills are VERY rusty,. I
would know how to do what I need using SQL Server's "Coalesce'
function, but I don't have that available to me in the Access
2002 database I'm currently programming.

...So could someone advise me how to add an Expression Field
to a Access 2002 Query that will reflect the following:

Imagine I want a field that derives from Fields in a table
I've called tblContacts. tblContacts has fields called
FirstName, LastName and CompanyName.

Here's what I want the Expression Field (which I'll call
'FullName') to reflect:

1) If neither FirstName nor LastName are NULL, then FullName
should be the equivalent of LastName, FirstName (that's the
LastName followed by a comma followed by the FirstName.

2) If LastName is NULL, but FirstName is not NULL, then
FullName should be the equivalent of FirstName.

3) If both LastName and FirstName are NULL, and CompanyName is
not null, then FullName should be the equivalent of
CompanyName.

So that's it. How would you 'design' this FullName field in
Access' Query Design view?

Thanks in advance.

You didn't specify a case where firstname is null but lastname
is not. The code below will show lastname,

Fullname: nz(Lastname+", " & Firstname, CompanyName)
Note the use of + and & as concatenation operators. + propagates
the null, (null + text = null), the & does not propagate the
null ( null & text = text). the Access nz() function handles
(value, alternate when value is null).

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Alan Mailer]

On 14 Sep 2006 00:14:28 GMT, Bob Quintal <rq******@sPAmpatico.ca>
wrote:

>Alan Mailer <cl**********@earthlink.netwrote in
news:49********************************@4ax.com :

>Ok, my Access 2002 language writing skills are VERY rusty,. I
would know how to do what I need using SQL Server's "Coalesce'
function, but I don't have that available to me in the Access
2002 database I'm currently programming.

...So could someone advise me how to add an Expression Field
to a Access 2002 Query that will reflect the following:

Imagine I want a field that derives from Fields in a table
I've called tblContacts. tblContacts has fields called
FirstName, LastName and CompanyName.

Here's what I want the Expression Field (which I'll call
'FullName') to reflect:

1) If neither FirstName nor LastName are NULL, then FullName
should be the equivalent of LastName, FirstName (that's the
LastName followed by a comma followed by the FirstName.

2) If LastName is NULL, but FirstName is not NULL, then
FullName should be the equivalent of FirstName.

3) If both LastName and FirstName are NULL, and CompanyName is
not null, then FullName should be the equivalent of
CompanyName.

So that's it. How would you 'design' this FullName field in
Access' Query Design view?

Thanks in advance.

You didn't specify a case where firstname is null but lastname
is not. The code below will show lastname,

Fullname: nz(Lastname+", " & Firstname, CompanyName)
Note the use of + and & as concatenation operators. + propagates
the null, (null + text = null), the & does not propagate the
null ( null & text = text). the Access nz() function handles
(value, alternate when value is null).

--
Bob Quintal

PA is y I've altered my email address.

Thank you Bob for this interesting solution. It got me almost all the
way to where I need to be; except for handling (as you mentioned) the
one condition I left out of my original message:

4) If LastName is not null and FirstName is Null, then FullName should
be the equivalent of LastName.

....By the way, one other quibble, your solution deals with condition
#2...

>2) If LastName is NULL, but FirstName is not NULL, then
FullName should be the equivalent of FirstName.

....in a way that is not optimal for me. (Please understand, I'm not
criticising your help, I really appreciate it). However, using your
solution, when the above condition is met FullName becomes
"FirstName," (in other words, the FirstName followed by an undesired
comma). Given this discovery, let me throw out a 5th rule I'd like
FullName to follow:

4) A comma should only appear in the FullName expression if both
LastName and FirstName are NOT NULL.

Could you (or anyone else reading this) help me to correct the
otherwise brilliant "Fullname: nz(Lastname+", " & Firstname,
CompanyName)" solution in a way that will accomodate the two new
conditions I've added in this message?

I'm learning a lot from this. Again, thank you for your help.

[Alan Mailer]

By way of update, here's how I've written the Query Expression I've
been asking about:

FullName: IIf(Not IsNull([FirstName]) And Not
IsNull([LastName]),[LastName] & ", " & [FirstName],IIf(Not
IsNull([LastName]),[LastName],IIf(Not
IsNull([FirstName]),[FirstName],[Company])))

....This appears to be giving me what I want, but it seems awfully
convoluted, not read-able, hard to write, etc...

I'm still open to hearing a cleaner way of going about this. In the
meantime though, thanks to all who have lent a hand.

On Thu, 14 Sep 2006 03:50:31 GMT, Alan Mailer
<cl**********@earthlink.netwrote:

>On 14 Sep 2006 00:14:28 GMT, Bob Quintal <rq******@sPAmpatico.ca>
wrote:

>>Alan Mailer <cl**********@earthlink.netwrote in
news:49********************************@4ax.co m:

>>Ok, my Access 2002 language writing skills are VERY rusty,. I
would know how to do what I need using SQL Server's "Coalesce'
function, but I don't have that available to me in the Access
2002 database I'm currently programming.

...So could someone advise me how to add an Expression Field
to a Access 2002 Query that will reflect the following:

Imagine I want a field that derives from Fields in a table
I've called tblContacts. tblContacts has fields called
FirstName, LastName and CompanyName.

Here's what I want the Expression Field (which I'll call
'FullName') to reflect:

1) If neither FirstName nor LastName are NULL, then FullName
should be the equivalent of LastName, FirstName (that's the
LastName followed by a comma followed by the FirstName.

2) If LastName is NULL, but FirstName is not NULL, then
FullName should be the equivalent of FirstName.

3) If both LastName and FirstName are NULL, and CompanyName is
not null, then FullName should be the equivalent of
CompanyName.

So that's it. How would you 'design' this FullName field in
Access' Query Design view?

Thanks in advance.

You didn't specify a case where firstname is null but lastname
is not. The code below will show lastname,

Fullname: nz(Lastname+", " & Firstname, CompanyName)
Note the use of + and & as concatenation operators. + propagates
the null, (null + text = null), the & does not propagate the
null ( null & text = text). the Access nz() function handles
(value, alternate when value is null).

--
Bob Quintal

PA is y I've altered my email address.

Thank you Bob for this interesting solution. It got me almost all the
way to where I need to be; except for handling (as you mentioned) the
one condition I left out of my original message:

4) If LastName is not null and FirstName is Null, then FullName should
be the equivalent of LastName.

...By the way, one other quibble, your solution deals with condition
#2...

>>2) If LastName is NULL, but FirstName is not NULL, then
FullName should be the equivalent of FirstName.

...in a way that is not optimal for me. (Please understand, I'm not
criticising your help, I really appreciate it). However, using your
solution, when the above condition is met FullName becomes
"FirstName," (in other words, the FirstName followed by an undesired
comma). Given this discovery, let me throw out a 5th rule I'd like
FullName to follow:

5) A comma should only appear in the FullName expression if both
LastName and FirstName are NOT NULL.

Could you (or anyone else reading this) help me to correct the
otherwise brilliant "Fullname: nz(Lastname+", " & Firstname,
CompanyName)" solution in a way that will accomodate the two new
conditions I've added in this message?

I'm learning a lot from this. Again, thank you for your help.

[David W. Fenton]

Alan Mailer <cl**********@earthlink.netwrote in
news:1q********************************@4ax.com:

By way of update, here's how I've written the Query Expression
I've been asking about:

FullName: IIf(Not IsNull([FirstName]) And Not
IsNull([LastName]),[LastName] & ", " & [FirstName],IIf(Not
IsNull([LastName]),[LastName],IIf(Not
IsNull([FirstName]),[FirstName],[Company])))

...This appears to be giving me what I want, but it seems awfully
convoluted, not read-able, hard to write, etc...

Nz(Mid(("12" + LastName) & (", " + FirsName), 3), Company)

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Bob Quintal]

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in
news:Xn**********************************@127.0.0. 1:

Alan Mailer <cl**********@earthlink.netwrote in
news:1q********************************@4ax.com:

>By way of update, here's how I've written the Query
Expression I've been asking about:

FullName: IIf(Not IsNull([FirstName]) And Not
IsNull([LastName]),[LastName] & ", " & [FirstName],IIf(Not
IsNull([LastName]),[LastName],IIf(Not
IsNull([FirstName]),[FirstName],[Company])))

...This appears to be giving me what I want, but it seems
awfully convoluted, not read-able, hard to write, etc...

Nz(Mid(("12" + LastName) & (", " + FirsName), 3), Company)

Wow, that is clever.
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Alan Mailer]

On Thu, 14 Sep 2006 16:17:54 -0500, "David W. Fenton"
<XX*******@dfenton.com.invalidwrote:

>Alan Mailer <cl**********@earthlink.netwrote in
news:1q********************************@4ax.com :

>By way of update, here's how I've written the Query Expression
I've been asking about:

FullName: IIf(Not IsNull([FirstName]) And Not
IsNull([LastName]),[LastName] & ", " & [FirstName],IIf(Not
IsNull([LastName]),[LastName],IIf(Not
IsNull([FirstName]),[FirstName],[Company])))

...This appears to be giving me what I want, but it seems awfully
convoluted, not read-able, hard to write, etc...

Nz(Mid(("12" + LastName) & (", " + FirsName), 3), Company)

Thank you for this suggestion. I'll try it today.

To my surprise, there is no online explanation for "Nz" in Access 2002
online help (at least none that I could find). If you (or anyone else
reading this) have another moment, could you explain to me exactly
what is being evaluated (and processed) in the statement you've
suggested? Don't be afraid of being verbose in your explanation, the
more detail the better. I say this because someone was kind enough to
attempt a shorthand explanation in an earlier message and I have to
admit I still didn't fully grasp what exactly (piece-by-piece) was
going on in the statement.

Thanks again for everyone's help!

[Alan Mailer]

How about I'll go one better and post my own explanation of what I
*think* the above expression means... and have someone tell me if (and
where) I'm wrong.

As an evaluation of the following Access query Expression

FullName: Nz(Mid(("12"+[LastName]) & (", "+[FirstName]),3),[Company])

....Here's my dissection:

Nz() :
The 'Nz' part will make sure that the first NON-NULL string in the
statement will be returned. In our current example, that will be one
of the following values:
- Mid(("12"+[LastName]) & (", "+[FirstName]),3)
- [Company]
Mid() :
("12"+[LastName]) & (", "+[FirstName]),3)
The 'Mid' statement above will start the resulting string from the 3rd
character in the string. For example:
- "12Smith, John" will become "Smith, John".
- "12Smith" will become "Smith".
- ", John" will become "John".
,[Company]) :
If the above 'Mid' statement evaluates as Null, Nz will cause
[Company] to be the result of the expression.
....Have I got this right?

[David W. Fenton]

Bob Quintal <rq******@sPAmpatico.cawrote in
news:Xn**********************@66.150.105.47:

"David W. Fenton" <XX*******@dfenton.com.invalidwrote in
news:Xn**********************************@127.0.0. 1:

>Alan Mailer <cl**********@earthlink.netwrote in
news:1q********************************@4ax.com :

>>By way of update, here's how I've written the Query
Expression I've been asking about:

FullName: IIf(Not IsNull([FirstName]) And Not
IsNull([LastName]),[LastName] & ", " & [FirstName],IIf(Not
IsNull([LastName]),[LastName],IIf(Not
IsNull([FirstName]),[FirstName],[Company])))

...This appears to be giving me what I want, but it seems
awfully convoluted, not read-able, hard to write, etc...

Nz(Mid(("12" + LastName) & (", " + FirsName), 3), Company)

Wow, that is clever.

I didn't originate it. But I can't say that I remember who came up
with it -- it was one of the regulars who isn't around much any
more, someone much cleverer than I.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[David W. Fenton]

Alan Mailer <cl**********@earthlink.netwrote in
news:c9********************************@4ax.com:

How about I'll go one better and post my own explanation of what I
*think* the above expression means... and have someone tell me if
(and where) I'm wrong.

As an evaluation of the following Access query Expression

FullName: Nz(Mid(("12"+[LastName]) & (",
"+[FirstName]),3),[Company])

...Here's my dissection:

Nz() :
The 'Nz' part will make sure that the first NON-NULL string in the
statement will be returned. In our current example, that will be
one of the following values:
- Mid(("12"+[LastName]) & (", "+[FirstName]),3)
- [Company]

Mid() :
("12"+[LastName]) & (", "+[FirstName]),3)
The 'Mid' statement above will start the resulting string from the
3rd character in the string. For example:
- "12Smith, John" will become "Smith, John".
- "12Smith" will become "Smith".
- ", John" will become "John".

,[Company]) :
If the above 'Mid' statement evaluates as Null, Nz will cause
[Company] to be the result of the expression.
...Have I got this right?

Yep.

You did leave out that in the Mid():

Null & Null

will return Null.

But I assume you understood that.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/