Results 1 to 5 of 5
  1. #1
    spidge32 is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2021
    Posts
    58

    Select Left 5 Char and 6 right characters in string

    I have a field in a table "wkouser" which is populated with 5 characters a dash and 6 characters (12345-USE100) I would like to update the value of another field SONO with the first 5 characters (12345) and another field CUS with the last six characters (USE100) for all the records in the table.


    I have tried using Left and right but cannot get the syntax right.

    Thank you in advance

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    UPDATE wkouser SET SONO = Left([someField],5) ?

    Always post what you tried if you can. It provides some context for those who would help you.
    Unless you plan to split the info and delete the field this may be a non-issue - you can populate any form/report field with the parts, same as you could update a table field.
    I answered on the basis that the number of characters in every record will always be as you posted. If not, you need something more robust.
    In that case, for the left part you could use Trim(Val([someField]). The rightmost part would be more involved.
    Last edited by Micron; 01-23-2022 at 07:45 PM. Reason: added info
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    what syntax have you tried? Where are you trying it? on a form or control?, in VBA?, in a query?

    and what does this mean?
    I would like to update the value of another field SONO with the first 5 characters (12345) and another field CUS with the last six characters (USE100) for all the records in the table.
    Do these two fields already have values and you are adding to the beginning or the end of those values? or are you just trying to populate blank fields?

    For SQL something like this should work

    UPDATE wkouser set sono=left(myfield,5), cus=right(myfield,6)

    obviously this will only work if your data is consistent

  4. #4
    spidge32 is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2021
    Posts
    58
    Thanks for your help. Your replies have saved me again. I was going around in circles trying to get the syntax correct.

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    I was going around in circles trying to get the syntax correct.
    use the query builder to build your query, then look at the sql it generates. Saves a lot of guessing

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

Similar Threads

  1. Forms query first three char string match .
    By aligahk06 in forum Forms
    Replies: 2
    Last Post: 08-03-2020, 02:01 AM
  2. Replies: 4
    Last Post: 02-24-2016, 04:43 PM
  3. Omitting characters from a string
    By TonyB in forum Queries
    Replies: 4
    Last Post: 04-07-2014, 08:03 AM
  4. how select middle characters on string ???
    By ayman.maher in forum Queries
    Replies: 1
    Last Post: 04-27-2010, 09:29 AM
  5. replace characters in a string
    By blazixinfo@yahoo.com in forum Access
    Replies: 6
    Last Post: 08-06-2009, 03:36 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