query statement does not update all records

[chaos18]

UPDATE X SET Source_ID = left([Source_ID],10) & "\"
WHERE x.Image_Source = "\Images\Rollfilm\";

This is a copy of my query i have to insert a "\" at the end of my column. Though when ran only about 1/3 of the cells in the column recieved a "\". I'm not sure what went wrong but i do know that all the information in that particular column and that corresponds with the "WHERE" statement have nine characters, which is the reason i set the number at ten spaces from the left. any suggestions on what i did wrong?

Thomas Gunter
Network Administrator A+, Network+

 

[gol4]

not sure but my gut would indicate it was in the select rather then the update since it did do a 1/3 of them
trouble shoot by

SELECT * FROM X
WHERE x.Image_Source = "\Images\Rollfilm\"
AND
left(X.[Source_ID],11) <> &quot;\&quot;;

to see if it produces the records if not drop the and on to see if it does.
Good luck

 

[chaos18]

thankyou for your input. i went in and ran the query and found that a majority of the characters when entered had a space at the end of the word (technically its not a word but for a lack of a better definition i'll call it that) and for it appears that the ones without the spaces were updated yet the ones with spaces werent, i donno y because it shouldn't discriminate, but rather just insert the &quot;/&quot; into the cell. I will just write a query to remove the space and re-run my query and that should do the trick hopefully

Thomas Gunter

 

[Transcend]

If you actually insert a space into a field
eg &quot;technology &quot;

then this is a different value to another field like so
&quot;technology&quot;

The string lengths are different and comparisons will show up as being different also.

Transcend

 

[chaos18]

ok so if i wanted to eliminate the slashes so i can be back at the beginning, could i possibly use a query to apply a filter to table X that lists all the entries that have a \ in it and then eliminate the \'s according to the results of the applied filter? Would that work or will i have to find another way. perhaps there is an easier way. Any ideas?

thanks