update query not updating
-
Can somebody tell me why the following isn't working?
UPDATE JobTicket_024 SET JobTicket_024.file = Replace([file_link],"\\52qjkl-as-004p","F:\CE\CE\CEDrawings");
I stole this from a previous update I did to the same table. Now I'm just changing the path again. It updates the field "file" with "\\qjkl-as-004p" not the new path. What gives? Thanks in advance. Windows 7 MS Office 10 (32 bit) Access 64bit machine
-
Can somebody tell me why the following isn't working?
UPDATE JobTicket_024 SET JobTicket_024.file = Replace([file_link],"\\52qjkl-as-004p","F:\CE\CE\CEDrawings");
I stole this from a previous update I did to the same table. Now I'm just changing the path again. It updates the field "file" with "\\qjkl-as-004p" not the new path. What gives? Thanks in advance. Windows 7 MS Office 10 (32 bit) Access 64bit machine
You are asking sql to look at the file_link column, find "\\52qjkl-as-004p" and replace it with "F:\CE\CE\CEDrawings" and put the result into the file column. Perhaps a before and after picture would help. However, instead of UPDATE just change to SELECT so you can see what it will do.
There are only 10 types of people in the world, those who understand binary and those who don't.
-
You are asking sql to look at the file_link column, find "\\52qjkl-as-004p" and replace it with "F:\CE\CE\CEDrawings" and put the result into the file column. Perhaps a before and after picture would help. However, instead of UPDATE just change to SELECT so you can see what it will do.
There are only 10 types of people in the world, those who understand binary and those who don't.
SELECT JobTicket_024.file_link, JobTicket_024.Project_Number
FROM JobTicket_024
WHERE (((JobTicket_024.file_link)=Replace([JobTicket_024]![file_link],"'\\52qjkl-as-004v\CE_Data\CE\CEC\oce_drawings'","'F:\CE\CE\CE Drawings'")));OK, first I changed UPDATE TO SELECT per your suggestion and I changed it to look at the "file_link" column and replace the "file_link" Interestingly, the query shows a table and then an error message pops up with "Data type mismatch in criteria expression" and as soon as I click "help" it clears the table. And, the field isn't updated either.
-
SELECT JobTicket_024.file_link, JobTicket_024.Project_Number
FROM JobTicket_024
WHERE (((JobTicket_024.file_link)=Replace([JobTicket_024]![file_link],"'\\52qjkl-as-004v\CE_Data\CE\CEC\oce_drawings'","'F:\CE\CE\CE Drawings'")));OK, first I changed UPDATE TO SELECT per your suggestion and I changed it to look at the "file_link" column and replace the "file_link" Interestingly, the query shows a table and then an error message pops up with "Data type mismatch in criteria expression" and as soon as I click "help" it clears the table. And, the field isn't updated either.
Don't put it in the where clause. Use the SELECT statement as a preview of what you want the update to do. For example:
SELECT file, file_link, Replace([file_link],'\\52qjkl-as-04v\CE_Data\CE\CEC\oce_drawings','F:\CE\CE\CE Drawings') AS NewFile
FROM JobTicket_024There are only 10 types of people in the world, those who understand binary and those who don't.
-
Don't put it in the where clause. Use the SELECT statement as a preview of what you want the update to do. For example:
SELECT file, file_link, Replace([file_link],'\\52qjkl-as-04v\CE_Data\CE\CEC\oce_drawings','F:\CE\CE\CE Drawings') AS NewFile
FROM JobTicket_024There are only 10 types of people in the world, those who understand binary and those who don't.
Thanks RyanDev, but still a no go. I copy/pasted your code to make sure I did not have any typos. It creates the new column, NewFile, but there were no changes to the resulting data. It just copied the old column to the new column verbatim.
-
Thanks RyanDev, but still a no go. I copy/pasted your code to make sure I did not have any typos. It creates the new column, NewFile, but there were no changes to the resulting data. It just copied the old column to the new column verbatim.
-
I know there were no changes. It is a SELECT statement, not an update statement. Perhaps run it again and paste some of the results here so we can see.
There are only 10 types of people in the world, those who understand binary and those who don't.
F#*#k There was a typo in my select statement. That's why it wasn't finding anything in the search/select column. Sorry for the troubles. Thanks for helping.
-
F#*#k There was a typo in my select statement. That's why it wasn't finding anything in the search/select column. Sorry for the troubles. Thanks for helping.