Deleting out partial data from a field
I need to delete out partial data from a field in Access. I have a field of numbers that begin with '1NF' ex.(1NF123424). This needs to be deleted, but leaving the number alone '123424'. How can I do this with out manualy deleting it out myself?
Software/Hardware used:
Software/Hardware used:
ASKED:
October 14, 2008 12:39 PM
UPDATED:
October 17, 2008 1:06 PM
Answer Wiki:
Sorry gave you wrong answer ... SUBSTR function is not available in Access (it is Oracle, DB2). Silly me! So use DWaltr suggested Right command. I played with it on access 2007 & works fine.
You can set up Access Update query in graphical design mode or by writing SQL. Access is very finicky about the proper syntax.
In design mode: Update To: Right([PartNo],(Len([Partno])-3))
Criteria: Like "1NF*"
On SQL use:
UPDATE tblChange SET tblChange.PartNo = Right([PartNo],(Len([Partno])-3))
WHERE (((tblChange.PartNo) Like "1NF*"));
(Note: PartNo is the field you are modifying)
Caveat -- be sure to make a copy of the table before you use this update command.
Good luck.
***********************************************************************************************
I was thinking along the same was as Dwaltr has suggested using substring command:
Update tblname set field=substr(field,4,10) where field like "1NF*";
Please check the format & parametrs -- 4 denortes the starting position, 10 denotes the length.
try using the right function
update tblname set field= right(field,len(field)-3);
This should remove the first three characters of field.
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
