Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    fomat a field in a query (access 2003)

    Is there a way to display only the numeruc value in positon 5 in the string below in a query? I know you can use right or left$ to trim off leading and ending characters but the problem is the only constant is that the number I want is usually in position 5 and is always proceeded by a - and followed by a _. Is there a way to only display what is in between the - and the _ in the query? Thanks..

    08V-1_Childhood Memories
    09V-2_Childhood Issues
    07V-3_Childhood development

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: fomat a field in a query (access 2003)

    Look up Mid in the VBA help.

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: fomat a field in a query (access 2003)

    Is it always a single digit number?

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: fomat a field in a query (access 2003)

    Yes Pat it is always a single digit 1 through 6...

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: fomat a field in a query (access 2003)

    Well do what Hans said.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: fomat a field in a query (access 2003)

    I got the mid function to working great to pull out the number 5 in the field below. The problem now is I have found that the numeric value is not always in postion 5. They told me it was but I as I looked at some older data I found that this is not the case. The only constant is that the numeric value is always between the - and the _. That being the only constant is there a way I can pull that numeric value out. I really appreciate the help. Thanks..

    Using: PromptName: Mid([Prompt name],5,1) I receive the value of 5 in my query....

    08V-1_Childhood Activity

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: fomat a field in a query (access 2003)

    Try
    <code>
    Mid([Prompt Name],InStr([Prompt Name],"-")+1,InStr([Prompt Name],"_")-InStr([Prompt Name],"-")-1)</code>

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: fomat a field in a query (access 2003)

    Hans I ran into an issue when I tried to query using the code you gave me. It was working great when the data was like the successful format but I got an invalid procedure call when I tried to query the failed data. Not sure what is going on. Do I have to modify the parameters you gave me? I really appreciate your help. Thanks...

    Successful:
    1A-2_Class optional vs required
    1A-2_Class optional vs required
    1A-5_Study Abroad
    1A-5_Study Abroad

    Failed
    07A-5_Power Failure
    07A-5_Power Failure
    07A-1_Surprise Accomplishment
    07A-1_Surprise Accomplishment
    07A-2_Second Language Requirement
    07A-2_Second Language Requirement
    07A-3_Student Art Gallery
    07A-3_Student Art Gallery
    07A-6-Empathy in Babies
    07A-6-Empathy in Babies
    07A-4_Entertainment Merchandising
    07A-4_Entertainment Merchandising

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: fomat a field in a query (access 2003)

    The expression works OK for me in most "failed" examples too except for the two occurrences of 07A-6-Empathy in Babies. This is because these contain a hyphen instead of an underscore after the number, so they don't fit the pattern that you gave us. See the attached sample database.
    Attached Files Attached Files

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: fomat a field in a query (access 2003)

    That was it Hans. Thanks so much..

Posting Permissions

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