Results 1 to 4 of 4
  1. #1
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185

    Find 2nd Initial

    Hi Guy's i am looking to grab the 2 initials of DrFullName, the code below will return the initial from forename and the last letter of surname, can i retrieve the first letter of surname ?

    DrFullName = "Joe Bloggs"
    Inits is set to a string


    Inits is returning J S

    Can i retrieve J B ?

    Code:
    FullName = DLookup("Name", "tblDealers", "[RecordNo] = " & RecNo)
    DelDate = DMax("DeliveryDate", "tblAssign", "[DelTo] = '" & FullName & "'")
    DrFullName = DLookup("Driver", "tblAssign", "[DelTo] = '" & FullName & "'" & " And [DeliveryDate] = #" & Format(DelDate, "mm/dd/yyyy") & "#")
    MyStr = InStr(DrFullName, " ")
    DrFName = Left(DrFullName, MyStr)
    DrSName = mID(DrFullName, MyStr)
    Inits = Left(DrFName, 1) & " " & Right(DrSName, 1)

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    This is why name parts should be in separate fields. What if name is like: Vincent Willem van Gogh and I hope you don't have a Cher or Madonna in your list.
    Consistency is critical in manipulating strings. Assuming first name always 1 word and no middle name/initial, then use:

    Left(DrFullName, 1) & " " & Mid(DrFullName, InStr(DrFullName, " ") + 1, 1)
    Last edited by June7; 06-11-2019 at 01:09 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    As june said... this way lies MADNESS! I would seriously look at splitting your first, last, middle, appellation (junior, III, etc) into separate fields are you are going to get inconsistent results

    I would approach it a little differently to handle middle names than June did though if your data is consistently first name/last name with no exceptions it will work

    left(drfullname, 1) & mid(drfullname, instrrev(drfullname, " ") + 1, 1)

    This will account for names like Michael J Fox, where the name may have multiple spaces in it with the assumption the text after the last space is the surname.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    So if last name is van Gogh or van der Walls, you are well on your way to MADNESS!
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Initial Design Help
    By Mattyn in forum Database Design
    Replies: 3
    Last Post: 07-05-2016, 04:10 PM
  2. Initial form not blank
    By tcgl in forum Forms
    Replies: 2
    Last Post: 08-09-2012, 10:13 PM
  3. Initial Design and Querying
    By TheBrigg in forum Access
    Replies: 2
    Last Post: 12-06-2011, 08:12 AM
  4. Need help on initial design
    By allykid in forum Database Design
    Replies: 2
    Last Post: 11-09-2010, 01:46 PM
  5. Initial Parameter Value?
    By justgeig in forum Queries
    Replies: 7
    Last Post: 12-08-2009, 05:18 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums