Help with VBA or macro programming

[David Campbell]

Hello all,

I've looked through a bunch of the news group postings and
all of the Access/VB books that I have and I can't find
the solution to this problem. Here it is:

I have a table with MembersFirstName, MembersLastName,
MatesFirstName, and MatesLastName then address, city,
state, etc.

What I need to do is look at the MembersLastName and
MatesLastName and determine the following:

' This would be a single person.
If MatesLastName = NULL then
OutputName = MembersFirstName + " " + MembersLastName
Endif

' This would be a married couple.
If MembersLastName = MatesLastName then
OutputName = MembersFirstName + " & " + MatesFirstName
+ " & " + MembersLastName
Endif

' This would be a person with a mate, but not married.
If MembersLastName <> MatesLastName then
OutputName = MembersFirstName + " " + MembersLastName
Endif

This code should run when the report is ran. I already
have a query setup to get the correct records.

TIA,
David Campbell

 

[Allen Browne]

Firstly, the expression "xx = Null" is never true. Use IsNull(xx). This is
one of 6 issues covered in article:
Common errors with Null
at:
http://allenbrowne.com/casu-12.html

Secondly, use "&" rather than "+" for concatenation in general. There is a
subtle difference:
"Fred" + Null => Null
"Fred" & Null => "Fred"

Try setting the Control Source for your text box to something like this:

=IIf([MembersLastName] = [MatesLastName],
[MembersFirstName] & " & " + [MatesFirstName] & " " & [MembersLastName],
[MembersFirstName] & " " & [MembersLastName] & " & " + ([MatesFirstName] &
[MembersLastName]))

If the comparison is True, you have a couple.
Otherwise, the last expression concatenates the mates first name and last
name together, and uses the plus to lose the ampersand if the result is null
(i.e. it's a single person).