Firstname plus Middlename??
in [Reports]
|
From: KG on 3 Jun 2010 15:56 Thank you so much! As they say, "a little knowledge is a dangerous thing". It all is working perfectly. "John Spencer" wrote: > 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: John Spencer on 3 Jun 2010 16:30 Steve, You have made an error in your expression. " " & MiddleName will always return at least a space. The plus sign will see the space and return it. If you wanted to be safe you could use [surname] & ", " & [firstname] & (" " + [middlename]) Although you could still end up with extra spaces if firstName and middlename are null you would end up with Spencer, and a space. "Spencer, " John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Steve wrote: > 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... > >
From: Duane Hookom on 4 Jun 2010 09:54 Steve, Apparently you didn't try my suggestion (or yours) before posting. I tested both of our expressions. I suggest you also test them. The whole value inside your parantheses (" " & [middlename]) will never be null. At the very least, it will be a single space. -- 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? "Steve" wrote: > 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... > > > . >
From: David W. Fenton on 4 Jun 2010 13:52 John Spencer <spencer(a)chpdm.edu> wrote in news:#D6fYg0ALHA.4652(a)TK2MSFTNGP06.phx.gbl: > 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. Put another way, the + operator used with strings propagates Nulls. That is actually quite useful, and I capitalize on it all the time. For example: Mid(("12"+LastName) & (", "+FirstName), 3) That will produce correct results when neither field is Null, when both are Null, and when either is Null and the other is not. It's much more efficient and easy to read than the nested IIf() tests that would otherwise be required. Beware, though, that if a field has a string in it that is coercable to a numeric value, you may end up with addition instead of concatenation. For instance, "12"+LastName could produce a numeric 100012 if, for instance, the LastName field contained the string "100000". This implicit type coercion is often quite helpful, but in this case, it wouldn't be. Note that it won't happen in *all* cases -- that very unpredictability is the reason why you have to be careful with it. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/
From: David W. Fenton on 4 Jun 2010 14:01 =?Utf-8?B?S0FSTCBERVdFWQ==?= <KARLDEWEY(a)discussions.microsoft.com> wrote in news:2590B05E-852C-4127-BCC5-E5247B246AFB(a)microsoft.com: > There are several ways including an IIF statement. > >=[surname] &", " & IIF([middlename] Is Null OR [middlename] = "", > [firstname], [firstname] & " " & [middlename]) But capitalizing on the Null propagation of the + concatenation operator can make for something much simpler (and more reliable in producing proper results): Mid(("12"+LastName) & (", "+(Trim(FirstName & (" "+MiddleName)))), 3) This handles more combinations of Nulls and non-Nulls than the formula above. For what it's worth, I long ago stopped storing middle initial/name as a separate field, as it's not an independent piece of data. The only scenario in which it's useful to separate it out is when you want to construct a salutation field -- "Dear David" is much nicer than "Dear David W.", of course. But you then gain another problem because you end up with the ambiguity of where to put data when someone has more than one candidate middle name/initial. I instead store a Salutation field so that where the default value is inappropriate, the user can fill in a proper value. This also allows for salutations not permanently tied to the name fields, such as Robert Smith's salutation can be "Dear Bob". To me, structuring your name storage for the purpose of constructing a saluation field is a wrong design. A salutation field is sufficiently independent to get its own data field, and that allows far more flexibility than is possible with deriving it from the name fields, and also allows the name storage to be simpler. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 Prev: Report only displaying some info Next: filrter several reports |