Results 1 to 8 of 8

Thread: SQL Queries Help Needed

  1. #1
    Criminal Master Mind ExarKun's Avatar
    Join Date
    Dec 1999
    Location
    Under The Police Station
    Posts
    1,118

    SQL Queries Help Needed

    Could someone who knows SQL better than me, tell me what the heck is wrong with this Query

    Code:
    SELECT CurrentStatus
    FROM tblrequest
    REPLACE ' Billed' 'New'
    In other words, I need to replace all items with the status, to new

    What am I missing, Its a Access XP database, does not Access support SQL Queries?


    Thanks
    Exar
    There Is only the Here and The Now, If you forget about that You might as well be Dead!

  2. #2
    Best In Show Noevo's Avatar
    Join Date
    Feb 2000
    Location
    SD
    Posts
    14,188
    Are you getting a nice juicy general sql error when you run that or does it just do nothing?


    try this and see what happens

    SELECT REPLACE(Column_name,'value_to_replace','value_to_replace_with') from your_table_name

    type it as if it were one line, it's being wrapped like it's 3 seperate lines for some reason

  3. #3
    Criminal Master Mind ExarKun's Avatar
    Join Date
    Dec 1999
    Location
    Under The Police Station
    Posts
    1,118
    Thanks Noevo, but it didn't work, it still keeps all the column unchanged

    Any other suggestion?
    There Is only the Here and The Now, If you forget about that You might as well be Dead!

  4. #4
    Regular Member
    Join Date
    Aug 1999
    Posts
    341
    Are you trying to replace the selected value, or replace the value in the table? If you are trying to replace the selected value, then you could do a UNION SELECT (do this in the SQL View):

    Code:
    SELECT " Billed" AS CurrentStatus
      FROM tblrequest
     WHERE CurrentStatus = "New"
    
    UNION
    
    SELECT CurrentStatus
      FROM tblrequest
     WHERE CurrentStatus <> "New";
    To replace the values in the table itself, an UPDATE will do the trick:

    Code:
    UPDATE tblrequest
       SET CurrentStatus = " Billed"
     WHERE CurrentStatus = "New";
    This assumes that the value of the field CurrentStatus contains only the string "New"--so, this will not work correctly if the value is "New Order", for instance. In that case, you'd have to use a function that replaces the token string "New" with the token string " Billed". I haven't used Access since '97, so you'd have to find out the function name from someone else.

  5. #5
    Best In Show Noevo's Avatar
    Join Date
    Feb 2000
    Location
    SD
    Posts
    14,188
    UPDATE Customers
    SET CurrentStatus = 'Billed'
    WHERE CurrentStatus = 'New';

    eh, sorry stu. just saw yours. heh, you gots options now snake

    snake, where/how are you trying to run the sql statement from?

  6. #6
    SG DC Team Member lance-tek's Avatar
    Join Date
    Apr 2001
    Location
    Indianapolis, IN
    Posts
    5,601
    I would go along with STU on that one looks like his code is pretty good. I always do a

    select w
    From x
    where y = z

    now if you do a union that is fine but I don't really see a need for it if you are just trying to do an update. in that case it would be

    set w
    where y = z
    A mistake does not become an error until one refuses to correct it

    Folding for the future

  7. #7
    Criminal Master Mind ExarKun's Avatar
    Join Date
    Dec 1999
    Location
    Under The Police Station
    Posts
    1,118
    Thanks guys, I'll try the queries when I get back to the office on Monday

    Exar
    There Is only the Here and The Now, If you forget about that You might as well be Dead!

  8. #8
    Criminal Master Mind ExarKun's Avatar
    Join Date
    Dec 1999
    Location
    Under The Police Station
    Posts
    1,118
    Ok none of those worked and heres why I found out even though the Column has text in it, the data is crosslinked to another table that has the samed named column but its fields are number fields, thats why I could not get it to work

    Cause its trying to write text to a number field only

    once I found the other table I was able to update it
    So thanks to all of you for your help

    Exar
    There Is only the Here and The Now, If you forget about that You might as well be Dead!

Bookmarks

Posting Permissions

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