Results 1 to 3 of 3
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post
    In an Access 2007 query, I built this very familiar expression:
    <P dir=ltr>Name: [Rank] & " " & [FName] & " " & [LName]

    I've used this and similar expressions dozens of times, but I've never seen the behavior I'm about to describe. In random records—not all—I see two spaces between the person's rank and the first name; or there are two spaces between the first and last names.

    More details: First and last names come straight from tblMembers. That table has a field for rank, which looks up values from a separate tblRanks.

    What really baffles me is the apparent random nature of this behavior—I cannot detect a pattern. I checked tblRanks and found no trailing spaces. I've checked all the first names—none had leading spaces, but a few had trailing spaces. I removed all the unnecessary spaces, but the behavior persists.

    This is certainly not a show-stopper, just an annoying puzzle. Any ideas?


  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    You could try using the trim function on both [FName] and [LName], but if it truly random, that wouldn't seem to work. By random do you mean that sometimes a given person has two spaces and sometimes he doesn't? Also how did you check for spaces? It may well be that you have some hard spaces, or some non-printing characters that appear as spaces. If you exclude the Rank and do a query that contains the function and then sort by the result you may be able to spot those that don't sort in sequence. And finally, I would use something other than "Name" as the name of the expression - that is a reserved word in Access and can cause all sorts of funny things to happen.
    Wendell

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post
    Thanks, Wendell! I'd forgotten about "Name" being a reserved word. I'll correct that tomorrow. I'll also try your suggestions.

    Re: random—I looked for any pattern, but could not find one. The extra space appears in some recsords where the rank is CPT (captain), but not in all. It appears in some records where the rank is Mr., but again not in all. I'm sure I've overlooked something.

Posting Permissions

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