Firstname plus Middlename??
in [Reports]
|
From: KG on 3 Jun 2010 13:31 Apologies if this is a silly question. I'm trying to combine Lastname, Firstname Middlename in a report. I have =([surname]+", "+[firstname]+" "+[middlename]). The only time it works is if all three fields contain values. As the majority of players don't have middle names in the database, most of the report is blank. Can anyone tell me what I'm doing wrong? Thanks so much for your help. Kind regards...
From: KARL DEWEY on 3 Jun 2010 13:54 There are several ways including an IIF statement. =[surname] &", " & IIF([middlename] Is Null OR [middlename] = "", [firstname], [firstname] & " " & [middlename]) -- Build a little, test a little. "KG" wrote: > Apologies if this is a silly question. I'm trying to combine Lastname, > Firstname Middlename in a report. I have > =([surname]+", "+[firstname]+" "+[middlename]). > The only time it works is if all three fields contain values. As the > majority of players don't have middle names in the database, most of the > report is blank. > Can anyone tell me what I'm doing wrong? > Thanks so much for your help. > Kind regards...
From: John Spencer on 3 Jun 2010 14:10 You should be using the & concatenate operator instead of the + operator. The plus returns blank if any of the values are null (blank/never entered). The & operator treats nulls as if they were a zero-length string ("") and so has no problem if any of the fields are blank - other than you may get some unwanted spaces. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County KG wrote: > Apologies if this is a silly question. I'm trying to combine Lastname, > Firstname Middlename in a report. I have > =([surname]+", "+[firstname]+" "+[middlename]). > The only time it works is if all three fields contain values. As the > majority of players don't have middle names in the database, most of the > report is blank. > Can anyone tell me what I'm doing wrong? > Thanks so much for your help. > Kind regards...
From: Duane Hookom on 3 Jun 2010 14:44 Try =[surname] & ", " & [firstname] & " " + [middlename] -- Duane Hookom Microsoft Access MVP NOTE: These public News Groups are ending June 1st. Consider asking questions at http://social.answers.microsoft.com/Forums/en-US/addbuz/threads? "KG" wrote: > Apologies if this is a silly question. I'm trying to combine Lastname, > Firstname Middlename in a report. I have > =([surname]+", "+[firstname]+" "+[middlename]). > The only time it works is if all three fields contain values. As the > majority of players don't have middle names in the database, most of the > report is blank. > Can anyone tell me what I'm doing wrong? > Thanks so much for your help. > Kind regards...
From: Steve on 3 Jun 2010 15:37
Duane's expression will give a space after the name if there is no middle name. The expression should be: =[surname] & ", " & [firstname] + (" " & [middlename]) When the plus sign is followed by parantheses, if anything is null inside the parantheses the whole value inside the parantheses becomes null. Thus, if there is no middle name, the string ends at firstname and there is no trailing space. Steve santus(a)penn.com "Duane Hookom" <duanehookom(a)NO_SPAMhotmail.com> wrote in message news:F2E7E72E-0B8F-40B0-98B9-E475442EF185(a)microsoft.com... > Try > =[surname] & ", " & [firstname] & " " + [middlename] > > -- > Duane Hookom > Microsoft Access MVP > > NOTE: These public News Groups are ending June 1st. Consider asking > questions at > http://social.answers.microsoft.com/Forums/en-US/addbuz/threads? > > > "KG" wrote: > >> Apologies if this is a silly question. I'm trying to combine Lastname, >> Firstname Middlename in a report. I have >> =([surname]+", "+[firstname]+" "+[middlename]). >> The only time it works is if all three fields contain values. As the >> majority of players don't have middle names in the database, most of the >> report is blank. >> Can anyone tell me what I'm doing wrong? >> Thanks so much for your help. >> Kind regards... |