Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2007
    Posts
    6

    Unanswered: InStr and Trim functions

    Does anyone have any sites or hints on how to use InStr and Trim functions? I've looked at the Microsoft site and their information is fairly basic. I am having difficulty with trimming from one character to another. I know it is just a learning issue but I'm having trouble finding examples or information.

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    In the code module, place your cursor in the word InStr or Trim, and press F1; the help file should give all the particulars for a given function. Give it a try, and let us know if you need more...
    Me.Geek = True

  3. #3
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    What does 'trimming from one character to another' mean? If you have
    Code:
     "   Hello, how are you?   "
    and you want
    Code:
    "Hello,howareyou?"
    there's no such VBA/Access function. Trim() will give you
    Code:
    "Hello, how are you?"
    Sam

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by Sam Landy
    If you have
    Code:
     "   Hello, how are you?   "
    and you want
    Code:
    "Hello,howareyou?"
    there's no such VBA/Access function.
    Why not
    Code:
    Replace("   Hello, how are you?   "," ","")
    ?
    Me.Geek = True

  5. #5
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Nick,

    I never heard of Replace() before. However, it seems from the Help file that it would work as you say.

    I stand corrected.

    Sam

  6. #6
    Join Date
    May 2005
    Posts
    1,191
    Learn something new everyday, eh?

    But back to the OP, have we helped you at all yet katsen, or do you have follow up question(s)?
    Me.Geek = True

  7. #7
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Learn something new everyday, eh?
    And how!

    Sam

  8. #8
    Join Date
    Jun 2007
    Posts
    6
    I appreciate the information. I was actually looking for examples to use in the design query, I'm just not good enough at the VB/module aspect. I wanted to learn how to use a TRIM function with an InStr function to trim say up to a comma, and then trim everything after a comma. For example: Trim(Mid([fieldname],InStr(1,[fieldname],":")+1,InStr(InStr(1,[fieldname],":")+1,[fieldname],":")-InStr(1,[fieldname],":")-6))

    I seem to have issues when I use other characters other than a comma and wanted to 'understand' the process. Maybe there are just some characters that do not work correctly, like "=" always gives me a compile error.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    thats a dangerous approach unless you know your data will always have the required character in it, and the original string will allways have characters in it. otherwise there is a risk that the function may truncate all characters, or cause a run time error.

    it may be smarter to put such logic into function that handles the error trapping gracefully.

    you need to fully get to grips with waht the help system says about the various sting manipulation functions such as left$,right$,mid$,instr etc...
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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