Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259

    Unanswered: Trimming Letters off a string

    I have a table with a specific field which has a type text; e.g DWE2001
    how would i trim the DWE off the field and create a new table with the value 2001?

    Can i use this in a query so that the criteria would be the number 2001 instead of the DWE2001?

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    is it always the last N chars you want? right$(whatever, N)
    or is it everything after the Nth char? mid$(whatever, N+1)
    or is it M chars after the Nth? mid$(whatever, N+1, M)



    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    yeah, there's always 3 letters before the four numbers. I just wanna trim that off

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by Jerrie
    yeah, there's always 3 letters before the four numbers. I just wanna trim that off
    As izy said, use the right$() function. In your case:

    right$(yrField, 4)

  5. #5
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    how would you integrate that into a query? would i put the criteria as,
    right$(ID#, 4)?

    it gives me error

    (sry, i'm not that great at coding queries)

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    are we still in Access?

    "SELECT * FROM blah WHERE right$(aField, 4) ='2001';"
    should fly

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by Jerrie
    how would you integrate that into a query? would i put the criteria as,
    right$(ID#, 4)?

    it gives me error

    (sry, i'm not that great at coding queries)
    Hashes are reserved characters in most languages. You will need to explicitly define the hash as part of the field name. Ideally you would remove the hash from the field name at the table level.

    right$([ID#], 4)

    Izy: Fancy running into you again eh?


  8. #8
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    I did this:
    SELECT Patent_Proper.ID_No
    FROM Patent_Proper
    WHERE ((Right$([ID_No],4)='2001'));

    it gives me error "compile error in query .."

    ID_No is a text field

  9. #9
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    how would you integrate that into a query? would i put the criteria as,
    right$(ID#, 4)?

    it gives me error

    (sry, i'm not that great at coding queries)

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Originally posted by Jerrie
    I did this:
    SELECT Patent_Proper.ID_No
    FROM Patent_Proper
    WHERE ((Right$([ID_No],4)='2001'));

    it gives me error "compile error in query .."

    ID_No is a text field
    no quotes around 2001

    WHERE ((Right$([ID_No],4)=2001));

    Or if you want to use quotes, use double quotes:

    WHERE ((Right$([ID_No],4)="2001"));

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    hi Teddy.

    somewhere up there, the id was stated to be text (and it's going to need to be text anyway for right$() to work), so i guess we are going to need to quote the 2001

    hi Jerrie.

    where is this SQL?
    in Access' query builder?
    in code?

    izy
    currently using SS 2008R2

  12. #12
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    Its in access; the query design mode (newbish); i know code's easier; love code but i know C++ only;

    nway, its in design, i'm puttin that as the the criteria.

  13. #13
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    then get to SQL-view of your query and use the double-quotes.

    SELECT *
    FROM myTable
    WHERE right$(myName, 1) = "r";

    finds "izyrider" just fine.

    izy
    currently using SS 2008R2

  14. #14
    Join Date
    Jan 2004
    Location
    Toronto, Canada
    Posts
    259
    (lol, how many posts for this i thought was simple)

    SELECT Patent_Proper.ID_No
    FROM Patent_Proper
    WHERE right$(ID_No, 4) = "1";
    even tried "2001", "rdsrdrs"

    nothing works

    does not work; compile error

  15. #15
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    hmmmn,

    if you are right$()ing the last four, the matching string needs to be four chars ("2001" and not "1")

    izy
    currently using SS 2008R2

Posting Permissions

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