Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2009
    Posts
    73

    Unanswered: MS Access Query required urgently

    My table has following as data

    Emp_No
    ---------
    ENAA/1
    AAN/22
    ASDF/999
    DSAF/12345


    OUTPUT REQUIRED

    Emp_NO
    --------
    00001
    00022
    00999
    12345

    please help

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    this exercise will get you to know your string functions.

    in a query, with side by side calculated fields you do a series of string manipulations:

    1. Instr to find the / position
    2. Count all characters
    3. Right() to trim out the / and all characters to the left i.e. Allcount-/position
    4. New count for remaining characters
    5. (5-NewCount) to determine the number of 0s needed
    6. Concatenate new 0s to left of remaining characters

    fun and games.
    www CahabaData com

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I'd use InStr() to find the starting point for the Mid() function. You can use the Format() function on the results of that to get the padded-zero output:

    Format(Mid(...InStr(...)...), "00000")
    Paul

  4. #4
    Join Date
    Aug 2009
    Posts
    73

    solved !!!! thanks

    SELECT
    format( mid(empid,instr(empid,'/') +1 ,len(empid)),"00000")
    FROM Table1;


    thank you so much it works

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Happy to help. FYI, you don't need the part in red

    format( mid(empid,instr(empid,'/') +1 ,len(empid)),"00000")

    The Mid() function will default to the length of the string if the length argument is omitted. It will work either way, I'm just throwing out an option. With a lot of data, it might be faster to omit it, since it won't have to process the Len() function.
    Paul

Posting Permissions

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