How do I delete out part of a field in Access?

[Guest]

I have several invoice numbers that begin in DM. For exmaple the invoice
number is DM123456. How do I run a query that will delete out the DM and
leave only the 123456. Note: Not all invoice numbers are the same length
so I can't right a simple Left function or the ones with shorter invoice
numbers will pull in the DM as well. Thanks!
-Jason

 

[Ken Snell \(MVP\)]

UPDATE MyTableName
SET InvoiceNumberField =
Replace(InvoiceNumberField, "DM","", 1, 1, 1);

 

[fredg]

I have several invoice numbers that begin in DM. For exmaple the invoice
number is DM123456. How do I run a query that will delete out the DM and
leave only the 123456. Note: Not all invoice numbers are the same length
so I can't right a simple Left function or the ones with shorter invoice
numbers will pull in the DM as well. Thanks!
-Jason

Update YourTable Set YourTable.FieldName = Mid([FieldName],3)
Where Left(YourTable.[FieldName],2) = "DM";

 

[John W. Vinson]

I have several invoice numbers that begin in DM. For exmaple the invoice
number is DM123456. How do I run a query that will delete out the DM and
leave only the 123456. Note: Not all invoice numbers are the same length
so I can't right a simple Left function or the ones with shorter invoice
numbers will pull in the DM as well. Thanks!
-Jason

To delete the leftmost two characters, regardless of the field length, use a
query updating the field to

Mid([invoiceno], 3)

I'd use a criterion of

LIKE "DM*"

to be sure you don't mess up records which don't have a DM in the field.

John W. Vinson [MVP]