Results 1 to 12 of 12

Thread: TRIM in ACCESS

  1. #1
    Join Date
    Jun 2005
    Posts
    115

    Unanswered: TRIM in ACCESS

    I must get a value without spaces in ACCESS

    SELECT ID
    FROM Materiel
    WHERE Serial = 'ABC'


    but ABC can be written with spaces before or/and after

    if i do

    SELECT ID
    FROM Materiel
    WHERE TRIM(Serial) = 'ABC'


    it doesn't work

    how can i do it ?

    thank you

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by quentin
    SELECT ID
    FROM Materiel
    WHERE TRIM(Serial) = 'ABC'
    That should work. Are their any spaces IN the text that you want to ignore? Also, how about:
    Code:
    SELECT ID
    FROM Materiel
    WHERE Serial LIKE '*ABC*'
    Does that work? If so, what you are seeing may not be spaces.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2005
    Posts
    115
    it works if I do

    SELECT ID
    FROM Materiel
    WHERE LTRIM(Serial) = 'ABC' OR RTRIM(Serial) = 'ABC'


    but of course it looks so heavy and TRIM should work , but it does not

    if a value = ABCD it will not work with LIKE *ABC* , I must TRIM

    thank you for helping

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Quentin

    the SQL was intended to be diagnostic not a solution.

    Quote Originally Posted by pootle flump
    Does that work? If so, what you are seeing may not be spaces.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jun 2005
    Posts
    115
    yes sorry , i have edited my post

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hmm - odd.

    Can you see which, in this instance, works (L or R Trim)?

    Are you able to make a copy of your db, strip out all other tables and all columns apart from the relevent one from the table, delete all other objects (basically set it up so that unless the relevent data is sensitive it will be safe to post) zip up the db and post?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jun 2005
    Posts
    115
    now it works with TRIM and i dont know why ! :-)

    thanks a lot for your time and help !

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the help system enlightens us thus:
    "LTrim, RTrim, and Trim Functions

    Returns a Variant (String) containing a copy of a specified string without leading spaces (LTrim), trailing spaces (RTrim), or both leading and trailing spaces (Trim).

    Syntax
    LTrim(string)
    RTrim(string)
    Trim(string)

    The required string argument is any valid string expression. If string contains Null, Null is returned."

    so effectively trim would do the same job as rtrim(ltrim(var)). As to why trim didn't work originally mebbe you'd bvest contact microsoft and register abug

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'd put a bet on a sytax error first time round - perhaps the real literal you were searching for had a typo?

    Anyhoo - H - what is an "imoderator"?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59

    off topic: whats an immoderator

    someone who is immoderate in all things..

    sort of summed up by the following:-
    Do things to excess...
    If you are going to be a bear be a grizzly....
    I used to like the Who's "hope I die before I get old", now that I am old (40+) I far prefer a certain Williams R.'s spin on that "hope I live before I die".....
    Encourage others to blow, or put aside, their own self imposed limits and throw caution to the wind....
    When you become recovered space on the datastore of life, you can't take it (or anything) with you - so go out and enjoy life

  11. #11
    Join Date
    Jun 2005
    Posts
    115
    yes sometimes it doesnt work, then you change nothing and it works ... difficult to understand why

    thank you for your help

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by quentin
    yes sometimes it doesnt work, then you change nothing and it works ... difficult to understand why
    Feel free to post your db as mentioned earlier.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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