SQL Queries Help Needed

[ExarKun]

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

Code: Select all

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

[Noevo]

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

[ExarKun]

Thanks Noevo, but it didn't work, it still keeps all the column unchanged

Any other suggestion?

[Stu]

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 all

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: Select all

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.

[Noevo]

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?

[lance-tek]

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

[ExarKun]

Thanks guys, I'll try the queries when I get back to the office on Monday

Exar

[ExarKun]

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