Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2007

    Question Unanswered: Field input question

    Hi All,

    I have one form which has field "Name" which houses an employee name in this format 'Doe, John' then I have an import button which takes the name filed and inputs it into another form with a "First name" and "Last Name" field. How would I get the import function to recognize that the comma separates the last and first name so when I click import the "Doe" goes in the other forms last name field and "John" goes in the first name field.

    Any ideas?

  2. #2
    Join Date
    Jun 2005
    Richmond, Virginia USA
    Provided Answers: 19
    Not real sure of exactly what you're doing when you say 'import,' but one way to parse out the names would be to use code like this:

    WholeName = "Anderson, Jack"

    LastName =  Trim(Left(WholeName, InStr(WholeName, ",")   1))
    FirstName = Trim(Right(WholeName", Len(WholeName)   InStr(WholeName, ",")))

    Linq ;0)>
    Hope this helps!

    The problem with making anything that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Oct 2007


    I have an import button that has this code:

    Forms![VOE Form]![Last Name] = Forms![Employment File]![Name]

    However on form Employment File the name field contains the first and last separated by a comma for example "Doe, John" but the VOE form has separate fields for first name and last name. I need the code to automatically place the first and last name from the one field to both fields on the VOE FORM.

    Example: when the EMPLOYEMENT FILE has value "Doe, John" in the "name" field in it's name field I need it to insert Doe in the "Last Name" field on the VOE form and John in the "First Name" field of the VOE form.

  4. #4
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    use a function to return the nth element

    Option Compare Database
    Option Explicit
    ' Procedure : GetElement
    ' Author    : healdem, posted on dBForums
    ' Date      : 04/09/2014
    ' Purpose   : returns a the specified element (ElementNo) in the supplied text(Item) separated by (Separator) the specified symbol
    ' if the function cannot find the symbol then if the element no is 1 return the whole string
    ' if the function cannot find specified element number then return ""
    'if the data is invalid then return what was supplied(Item)
    Public Function GetElement(Item As String, ElementNo As Integer, Optional Separator As String = ",") As String
    GetElement = Item 'set up or default return value
    'OK as we don't trust the user lets do some validations
    'item must have something in it
    If IsNull(Item) Or Len(Item) < 1 Then Exit Function
    'the select item MUST be postiive and greater than 1
    If ElementNo <= 0 Then Exit Function
    'the separator must have something in it
    If IsNull(Separator) Or Len(Separator) < 1 Then Exit Function
    'ok so we think we have valid data
    Dim Words() As String   'this will hold the individual words in the input
    'split the input text into words separated by the specified character
    Words = Split(Item, Separator)
    NoWords = UBound(Words) + 1 'Access Arrays are zero based so add 1 to tge the actual nuber oif words found
    If ElementNo <= NoWords Then    'we have a word fopr that element no
        GetElement = Words(ElementNo - 1)
    Else    'we didn't find anything so return nothing
        GetElement = ""
    End If
    End Function
    firstword = getelement(mytext,1,",")
    secondword = getelement(mytext,2,",")

    the separator is optional so
    firstword = getelement(mytext,1)
    secondword = getelement(mytext,2)
    is the same as
    firstword = getelement(mytext,1,",")
    secondword = getelement(mytext,2,",")

    a note of caution NAME is a reserved woird in Access/JET using it may cause problems
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Oct 2007

    I have a command button that imports the name field from one form to another. The issue I have is the name field on the one form is just "NAME.text" and contains the first and last name separated by a comma. The form I'm importing to has separate first and last name fields. When I click my command, I want it to automatically put the first and last name in the correct fields on the firm that has two fields instead of one. Below is the code, but it's not splitting the name up currently.

    Forms![VOE Form]![Last Name] = Forms![Employment File]![Name]

    VOE Form contains both first and last name fields. Employment file form just contains field name with the full name separated by a comma.

Posting Permissions

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