Results 1 to 2 of 2
  1. #1
    chris31804 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2017
    Posts
    1

    Use VB code to display individual words from a Field

    I need help displaying individual words from a Data Filed in Access 2016 using VB code. The Data Field contains the following: John Price Plattsburgh NY 12901. I need to write VB code that will display the each word in Form TextBox. There is a button to activate the splitting the of the data. The code I have so far is as follows:

    Option Compare Database
    Dim y As Integer

    Private Sub Command6_Click()


    y = InStr(Data, " ")
    First_Name = Left(Data, y)
    Last_Name = Right(Data, Len(Data) - y)

    End Sub

    This code successfully displays the first name of the Data Field. it displays the Last name as well, but it also displays the rest of the string. I need to separate each word and display in the form.

    I have included a screenshot of the Table where the data is and the Form that needs to display it.

    Click image for larger version. 

Name:	Table.PNG 
Views:	9 
Size:	24.9 KB 
ID:	28313 Click image for larger version. 

Name:	Form.PNG 
Views:	9 
Size:	9.4 KB 
ID:	28314

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    the easiest way is probably to create a udf function.

    In a new module (not a form module) create the following - you can just copy and paste this code

    Code:
    Option Compare Database
    Option Explicit
    
    public function splitStr(str as string, element as integer) as string
    dim strArr() as string
    
    strArr=split(str," ")
    splitstr=strArr(element)
    end function
    then in your form recordsource call it as

    FirstName:splitStr(data,1)
    LastName=splitStr(data,2)
    etc

    this relies on there being the same number of elements in each string and you don't have for example some entries where the person has a middle name, or a zipcode has not been included

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

Similar Threads

  1. Replies: 3
    Last Post: 11-12-2016, 05:12 PM
  2. Selecting a few words from a field to display
    By bryant03 in forum Queries
    Replies: 1
    Last Post: 06-07-2012, 08:55 AM
  3. Find multiple words within Code
    By Mooo0 in forum Programming
    Replies: 1
    Last Post: 03-07-2012, 10:54 PM
  4. Display Individual Records in Report
    By Luke in forum Access
    Replies: 2
    Last Post: 07-13-2011, 12:18 PM
  5. Code to display latest record number field created?
    By rowardHoark in forum Programming
    Replies: 1
    Last Post: 01-31-2011, 08:03 AM

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