Deleting out partial data from a field

15 pts.
Tags:
Microsoft Access
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?

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following