Trim leading characters

[Guest]

Let me start by saying I am very new to Access and would appreciate any help
you experts can give me. I need to remove some leading characters in one
column. The problem is the leading character is not always the same. The
field is 8 characters long and the leading characters will vary from 1-7
zero’s or the letter “M†and 1-6 zeros or the letter “Mâ€.

For example the column name is CustNum and the data in the column is
Currently I want it to look like:
M0007202 7202
M0044863 44863
M1317017 1317017
00007132 7132
00754604 754604

To warn you I am so new to Access I would also need to know where to go to
enter/paste any code you may provide. Please take pity on a newbie and help
him out.

Thanks much in advance.

 

[Guest]

Assuming that the first character, either M or 0, can always be trimmed off
and there are no other non-numerical characters in the other 7 positions:

Trimmed: Val(Mid([FieldName],2))

This goes in a query in the Field row of the QBE design grid. Change
"FieldName" above with the actual field name.

Mid is a function that extracts characters from a string of text. The 2
means start at the second character. The Val function returns the numeric
portion of a string as a number. Therefore it dumps the leading zeros as
numbers don't have them.

 

[Joseph Meehan]

Let me start by saying I am very new to Access and would appreciate
any help you experts can give me. I need to remove some leading
characters in one column. The problem is the leading character is
not always the same. The field is 8 characters long and the leading
characters will vary from 1-7 zero's or the letter "M" and 1-6 zeros
or the letter "M".

For example the column name is CustNum and the data in the column is
Currently I want it to look like:
M0007202 7202
M0044863 44863
M1317017 1317017
00007132 7132
00754604 754604

To warn you I am so new to Access I would also need to know where to
go to enter/paste any code you may provide. Please take pity on a
newbie and help him out.

Thanks much in advance.

Assuming that the first character will always be trimmed and that after
trimming that first character no alpha characters will remain. I would add a
new field to the table and use an update query to populate it with the data
from the existing filed while removing the first character remove the first
character. You can use this formula:

Right([Fieldname], 7)

This should give you:

0007202
0044863
1317017
0007132
0754604

Then change the field type to integer which should result what you want.

 

[Guest]

Thanks so much for the quick response. This is exactly what I needed.

Let me start by saying I am very new to Access and would appreciate
any help you experts can give me. I need to remove some leading
characters in one column. The problem is the leading character is
not always the same. The field is 8 characters long and the leading
characters will vary from 1-7 zero's or the letter "M" and 1-6 zeros
or the letter "M".

For example the column name is CustNum and the data in the column is
Currently I want it to look like:
M0007202 7202
M0044863 44863
M1317017 1317017
00007132 7132
00754604 754604

To warn you I am so new to Access I would also need to know where to
go to enter/paste any code you may provide. Please take pity on a
newbie and help him out.

Thanks much in advance.

Assuming that the first character will always be trimmed and that after
trimming that first character no alpha characters will remain. I would add a
new field to the table and use an update query to populate it with the data
from the existing filed while removing the first character remove the first
character. You can use this formula:

Right([Fieldname], 7)

This should give you:

0007202
0044863
1317017
0007132
0754604

Then change the field type to integer which should result what you want.