Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185

    Spaces in String

    Hi Guys, hope everyone is staying safe in these difficult times we are all in ?

    I am trying confirm from a text box how many spaces there is and get each word between spaces

    This is just a piece of code i am thinking of:

    Dim myClient as String
    Dim MyLeft as String
    Dim myMid as string
    Dim myRight as String
    Dim NameResult as String

    myClient = Me.Client

    'Some Kind Of Space Count

    if spaces = 3 ie Mr Donald Duck

    If Instr(myClient," ") <> 0 Then
    myLeft = Left(myClient)
    myMid = InsTr(" " & myClient," ")
    myRight = Instr(" " & " " & myClient)

    NameResult = myLeft & " " & myMid & " " & myRight (Mr Donald Duck)



    If Spaces = 2 ie Donald Duck
    NameResult = myLeft & " " & myRight (Donald Duck)

    If Spaces = 4 ie Mr Donald Andrew Duck
    Unsure how to do this ie if client has got a double barrel surname

    I maybe incorrect in the method i have just written

    Any advice or adjustment would be grateful

    Kindest

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'm not clear on what the goal is. I'd probably use the Split() function, which would be more dynamic. This gives you the number of spaces, though it's sort of by accident:

    ?ubound(split("Mr Donald Andrew Duck"))
    3

    Another way:

    ?len("Mr Donald Andrew Duck") - len(replace("Mr Donald Andrew Duck", " ", ""))
    3
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    If you want to "each word between spaces" I'd use the Split() function, looping with 0 and UBound().
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Thank you pbaldy, would be so kind enough to write a quick example ?

    How to return Mr Donald Duck
    answer: Mr Duck

    How To Return Mr Donald Andrew Duck
    answer: Mr Andrew Duck (missing Donald out)

    How To Return Donald Duck
    answer: Donald

    All subject to spaces, i kind of understand your reply just never really worked with UBound in VBA

    Much appreciated

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Heading to lunch. Split() gives a zero based array. That means 0 is the first value, 3 is the last value (in the example I used of yours). In the example I used, those would be "Mr" and "Duck" (1 and 2 would have the other values). I don't see the pattern in what you want to return so not sure how to code it.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Here's the basic loop, you can play with it:

    Code:
        Dim astrLines()           As String
        Dim L                     As Long
    
        astrLines = Split(YourValueHere, " ")
        For L = 0 To UBound(astrLines)
            Debug.Print L & " : " & astrLines(L)
        Next L
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Hi pbaldy, thank you, i have got as far as the code pasted and returns 1 if 1 space and 2 if 2 spaces,

    a: So if the ClientName = Mr Donald Duck

    I am wanting to return Mr Duck

    b: If the Client Name = Donald Duck

    i am wanting to return Donald

    case a: above is so an auto response can say Hello Mr Duck
    case b: above is so an auto response can say Hello Donald

    I am finding it difficult to return the desired word if there is 1 space or 2 spaces or even 3 spaces if the surname is double barrel name

    Code:
    Dim myName As StringDim myLeft As String
    myName = UBound(Split(Me.txtClientName))
    MsgBox myName
    Last edited by DMT Dave; 10-28-2020 at 03:30 PM. Reason: changing code

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You need a set of rules, like what to return if there's 1 space, 2 spaces, 3 spaces, etc. Then you could use Select/Case and say if UBound is 0 (1 word only) return it, if 2 return the first word, etc. Along the lines of:

    Code:
      Dim astrLines()           As String
      Dim L                     As Long
    
      astrLines = Split("Donald Duck Jr", " ")
        
      Select Case UBound(astrLines)
        Case 0
          MsgBox "Hello " & astrLines(0)
        Case 1
          MsgBox "Hello " & astrLines(1)
        Case 2
          MsgBox "Hello " & astrLines(0) & " " & astrLines(2)
      End Select
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Think your are setting yourself a very difficult task as there endless variations in which a name can be presented. Using your example A, title might be Mr, Mrs, Ms, Mz, Dr, Professor, Prof, Sir, Lord etc. And what if all you have is Mr Duck? I recently received a letter from an organisation addressed to (I assume my late mother) Mrs Deceased.

    Better to split the name into various fields - perhaps using the suggestions Paul has made and then manually correct as necessary then you can do what you want. Plus storing names in their component parts is worthwhile as you can then make use of indexing

    Alternatively create a 'salutation' field which is used in correspondence. This would also cover the situation where the first name is 'Donald' but you want to be able to say 'Hello Donny'.

    Just depends on how flexible you need to be and how 'simple' the name structures are that you actually have.

  10. #10
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Thank you Ajax and pbaldy, all appreciated, just to put you in the picture, there are 2 cases here so i think post 8 will nail it for me.

    case 1, the client name is always taken from our website so there are 3 fields on the website:
    Title
    Forename
    surname

    So when these strings are pulled from the website, there will always be 3 names (2 spaces)

    Case 2, we manually add the record our selves from a phone call, we may only put Donald Duck therefore there is 1 space in the 2 names.

    If all names were taken from the website, it would be easy as there would always be 2 spaces because a client has no choice but to fill in title, forename and surname

    Hope this clarifies the reason for this code

    but thanks again guy's

  11. #11
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Hi again guy's great got it working based on post 8, i can't thank you guy's enough , this now responds with the correct response

    ie: Good morning Mr Duck or Good morning Donald depending on time of day(TOD) and your post 8 method adapted below, happy days

    Code:
    Dim TimeNow As String, TOD As String, FullName () As String, myClient as String
    FullName = Split(Me.Client, " ")
    Select Case UBound(FullName)
    Case 0
    myClient = FullName(0)
    Case 1
    myClient = FullName(1)
    Case 2
    myClient = FullName(0) & " " & FullName(2)
    End Select
    If TimeNow <= "11:59" Then
    If TimeNow >= "00:01" Then
    TOD = "Good morning" & " " & myClient
    End If
    End If
    If TimeNow <= "16:59" Then
    If TimeNow >= "12:01" Then
    TOD = "Good afternoon" & " " & myClient
    End If
    End If
    If TimeNow <= "23:59" Then
    If TimeNow >= "17:01" Then
    TOD = "Good evening" & " " & myClient
    End If
    End If

  12. #12
    moke123's Avatar
    moke123 is offline Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    You can probably simplify your time selection with something like this

    Code:
        Dim dte As Date
        Dim msg As String
    
        dte = TimeValue("01:00") ' use your time value here
    
        Select Case dte
    
        Case Is < TimeValue("12:00")
            msg = "Good morning "
    
        Case Is < TimeValue("16:00")
            msg = "Good afternoon "
    
        Case Else
            msg = "Good evening "
    
        End Select
    
        MsgBox msg
    
    End Sub
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  13. #13
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by DMT Dave View Post
    ..the client name is always taken from our website so there are 3 fields on the website...
    and then in next day you get some spaniard guy as client, which fills in his forename: Jose Maria Manuel...

  14. #14
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Thank you everyone, next question, is it easy to format a recordset field within setting teh recordset ie:

    Set rs = Currentdb.OpenRecordset("tblJobs.JobNo, " & Format(tblJobs.JobDate,"ddd-dd-mmm-yyyy") & ", tblJobs.NextField, tblJobs.NextField etc etc

    This is the output from a criteria exports in the date formatted Wed-28-Oct-2020

  15. #15
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Yes, but it would be more like:

    Set rs = Currentdb.OpenRecordset("SELECT tblJobs.JobNo, Format(tblJobs.JobDate,'ddd-dd-mmm-yyyy') AS Whatever, tblJobs.NextField...
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 04-26-2020, 02:01 AM
  2. Trimming Internal Spaces from a String
    By J Bhujanga in forum Queries
    Replies: 2
    Last Post: 02-27-2017, 04:51 PM
  3. Replies: 8
    Last Post: 01-30-2014, 01:43 PM
  4. calc field producing unwanted spaces amidst the final string
    By kattatonic1 in forum Database Design
    Replies: 7
    Last Post: 07-12-2013, 10:32 AM
  5. Look Up table with string that contain spaces
    By Leonidsg in forum Database Design
    Replies: 1
    Last Post: 04-03-2013, 06:53 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