Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Nulls/Empty String in Query (A2k3, SP1)

    Hi All,
    I've been using the following concatenation in personal information queries for quite some time:

    FirstLast: [Salutation] & (" "+[FirstName]) & (" "+[MiddleName]) & (" "+[LastName])

    In a certain app, I just recently I noticed that:

    if all 4 elements are present the query returns (using <sp> as a space):
    Ms.<sp>Jane<sp>P.<sp>Doe

    if the salutation is missing:
    <sp><sp>Jane<sp>P.<sp>Doe

    if the middle name is missing:
    Ms.<sp>Jane<sp><sp>Doe

    if there is only a last name:
    <sp><sp><sp>Doe

    and so on........

    So I checked and sure enough, a bunch of the field entries contained an empty string instead of Null, which of course is the cause of the <spaces> left in the concatenation. I know that sometimes when entering data, if a user just backspaces any characters typed and then leave that field, the value left in the field can be an empty string, rather than Null.
    Is there any way I can prevent/work around/fix without running an update query prior to the personal info query?
    In code, no problem, but I really do wish to stay with a query on this one.
    Thanks.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Nulls/Empty String in Query (A2k3, SP1)

    A slight rearrangement should take care of the first space:

    FirstLast: ([Salutation]+" ") & ([FirstName]+" ") & ([MiddleName]+" ") & [LastName]

    But if you get multiple spaces, that indicates that the FirstName and MiddleName fields aren't really null, but contain an empty string. The following more elaborate expression should take care of that:

    FirstLast: [Salutation] & IIf(Nz([Salutation])="",""," ") & [FirstName] & IIf(Nz([FirstName])="",""," ") & [MiddleName] & IIf(Nz([MiddleName])="",""," ") & [LastName]

  3. #3
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Nulls/Empty String in Query (A2k3, SP1)

    Thanks Hans.
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •