Results 1 to 3 of 3
  1. #1
    Juan4412 is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Dec 2010
    Posts
    209

    Split In Query

    I have been using VBA to split and I have come up with this function


    Code:
    Function Split()
    Dim str As String: str = ".13_Bill Tinsley"
    Dim str1 As String
    Dim strNumber As String
    Dim strName As String
    
    str1 = Split(str, ".")(1)
    strLevel = Split(str1, "_")(0)
    strName = Split(str1, "_")(1)
    
    End Function
    which would give me the Level and the player name

    I have a table that has a field called playerLvlAndName - that I want to split into two fields using an insert query. Using a query, how can I split the field playerLvlAndName so that I can insert the value of strLevel = Split(str1, "_")(0) into Level and the value of strName = Split(str1, "_")(1) into Name?

    I want to insert the values Bill Tinsley into Name, and 13 into Level

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    That function doesn't really accomplish anything. It does not return any value to calling source.
    Code:
    Function GetData(intI As Integer, strS As String) As Variant
    GetData = Split(Mid(strS, 2), "_")(intI)
    End Function
    UPDATE tablename SET [Level]=GetData(0, [fieldname]), [Name]=GetData(1, [fieldname])

    Or eliminate VBA:

    UPDATE tablename SET [Level]=Val(Mid([fieldname],2)), [Name]=Mid([fieldname], InStr([fieldname], "_")+1)

    Name, Level, Split are all reserved words and should not use reserved words as names for anything.
    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
    Juan4412 is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Dec 2010
    Posts
    209
    Thanks @June7 your Val(Mid()) and Mid(InStr()) functions saved the day!

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

Similar Threads

  1. Split int in query
    By Carloj in forum Programming
    Replies: 5
    Last Post: 10-31-2018, 01:52 AM
  2. Split A Query Into Two Text Files?
    By kestefon in forum Access
    Replies: 10
    Last Post: 01-24-2014, 10:31 PM
  3. Split form on append query
    By OneToLearn in forum Queries
    Replies: 1
    Last Post: 04-16-2012, 11:20 PM
  4. Split Record Query
    By Flippa in forum Queries
    Replies: 1
    Last Post: 12-15-2011, 05:27 PM
  5. Split data by using query
    By lamkee in forum Queries
    Replies: 1
    Last Post: 11-07-2010, 11:23 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