Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2008
    Location
    Suburb of Chicago, Illinois
    Posts
    56

    Cool Unanswered: Parsing Employee Name field

    My table contains a single field for EmployeeName populated as lastname, firstname
    In my report query qry_OptimizeIt1 I defined two new fields as Expr4: ParseFirstComp([OrderRepName]) and Expr2: ParseSecondComp([OrderRepName])
    In my report, for text box RepName, my Control Source is =StrConv([Expr2] & " " & [Expr4],3) which changes the name to FirstName LastName,
    My problem is that I end up with a comma after the LastName.
    Can someone help me determine how to eliminate the comma?


    Thanks,

    Krazy (Bill) Kasper

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why don't we fix this at the source?
    Change your table to have a firstname and lastname column to replace your employeename column, this makes the problems you're experiencing trivial.
    Code:
    lastname & ", " & firstname
    firstname & " " & lastname
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    use a function.....

    Code:
    public function TartUpName(AName as string) as string
    'This function is designed to split a supplied name based on a comma delimited list. it only works with 2 components (eg "Janus, Hugh")
    'it will rebuild the incoming parameter by transposing the two terms separated by a comma
    'the function could be generalised to handle a different separation character
    'it will have a problem with names supplied with two or more commas.. the function can be extended to handle that if desired
    
    AName=strconv(aName, vbProperCase) 'convert the incoming name to Proper Case eg JANUS, HUGH becomes Janus Hugh
    if isnull(aName) or strlen(aName<1) then 'test if we've been sent crap data
      TartUpName=""
    else
      Dim Names() as string 'this will receive the output of the split function
      Names=split(AName,", ") 'break apart the incoming name into 2 or more elements based on the comma
      'Do we need to be cute and handle special cases like MacDonald, O'Connor etc?
      'for now lets assume not.....
      'rebuild the name
      'so Janus, Hugh becomes Hugh Janus
      TartUpName=names(1) & " " & names(0)
    endif
    code is made up on the fly so it will need debugging

    Got to agree with GeorgeV though... it would be far better to handle this at source
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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