Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Nov 2008
    Posts
    117

    Unanswered: Searching and Replacing Datas

    Hello sir,
    I am using Ms-Access 2003

    I having some records in recordset.
    recordset name: check
    Field name : no
    Field Type : text

    11-3, 5-2, 4, 5 (these are the datas in "no" field)
    Not only these datas, there are also more datas

    Now what i want is changing "-" with "/" so 11-3 must become 11/3

    And also other datas with "/1" (ie) 4 must become 4/1

    I need coding for this (needs to be an SQL query for changing this)

    Thank you sir.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    UPDATE myTable
    SET no = REPLACE(no, "-", "/")
    WHERE no LIKE "*-*"
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by bharanidharanit
    And also other datas with "/1" (ie) 4 must become 4/1
    You'll need to explain this part of the requirement more fully.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Nov 2008
    Posts
    117
    Hello sir,
    How to execute the given query in VB.
    Thank you sir.

  5. #5
    Join Date
    Feb 2004
    Posts
    214

    I believe it has something to do with this

    http://www.dbforums.com/showthread.php?t=1635368

    they are importing fractions as text, splitting the string before and after the "/" into two fields and then dividing them. Since 4 is a whole number, the string can't be split. But it should have been addressed in the attachment with an iff statement.
    You know, I'm sick of following my dreams, man. I'm just going to ask where they're going and hook up with 'em later

  6. #6
    Join Date
    Nov 2008
    Posts
    117
    Hello sir,
    That link contains posts which contains data to be converted into fraction. But here i need something different.
    Please check post1
    Thank yous sir.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Thanks Mr Blonde. I think there was actually an even earlier post than that one that I responded to with EVAL().
    bharanidharanit - these look like extensions of one problem. Please just keep one thread going until fully resolved otherwise people are acting on partial information.

    If anyone wants me to I can merge all these threads so there is one story.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by bharanidharanit
    Hello sir,
    How to execute the given query in VB.
    Thank you sir.
    Code:
    CURRENTDB.EXECUTE "Insert your SQL here", dbFailOnError
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Nov 2008
    Posts
    117
    Hello sir,
    I used this coding.
    CURRENTDB.Execute " UPDATE myTable, Set no = Replace(no, " - ", " / "),Where no Like "*-*", dbFailOnError"
    I am getting compile error as Expected Expression

    Thank you sir.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That'll be because you didn't escape the quotes. Strings are enclosed in quotes, so if your string includes some you've got to escape them. Also, dbFailOnError is a parameter for the Execute method. So:
    Code:
    CURRENTDB.Execute " UPDATE myTable Set no = Replace(no, "" - "", "" / "") Where no Like ""*-*""", dbFailOnError
    You've also added some spaces to the code I posted - I hope that was intentional! I've removed the definitely wrong commas.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Feb 2004
    Posts
    214

    ...

    nevermind....
    You know, I'm sick of following my dreams, man. I'm just going to ask where they're going and hook up with 'em later

  12. #12
    Join Date
    Nov 2008
    Posts
    117
    Hello sir,
    It is showing syntax error in update statment during runtime

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I would imagine no is a reserved word (as such it is a bad choice of name for any object\ column): http://support.microsoft.com/kb/286335
    Change no to [no].
    I hope you changed the table name!
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Nov 2008
    Posts
    117
    Sir,
    I changed and now also the same error

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Please copy and paste your code.
    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
  •