Remove leading Apostrophe

[Natalie]

I know this maybe very simple, but I can't seem to get my formulat to work.
I need to remove the leading apostrophe in a numeric field from a text file.
When I use the replace function I get an "ERROR# in fields without data.

How can i strip away the apostrophe from these numbers?

'100334613071008899

I think there is a function I can use but I don't know which one it is.

Thanks in advance.

 

[Jason]

Natalie,

Try this:
NewfieldName: Right([CurntFieldName],Len([CurntFieldName])-1)

What this will do is count the total # of characters in the field (Len),
subtract 1 from that number and then pull that # of characters from the
field, starting from the right (Right).

Access should, allon it's own see the new field as a number. If it doesn't
wrap the whole formula in a Val( ) function:
NewfieldName: Val(Right([CurntFieldName],Len([CurntFieldName])-1))

Hope this helps,

Jason

 

[John Spencer]

Do all the fields have an apostrophe unless they are null (blank)?

If so, you could use

Mid([The field],2)

Otherwise you would need to test for the presence of the apostrophe
IIF([The Field] like "'*",Mid([The field],2),[The Field])

Or if the apostrophe has a leading space or other character(s) in front of
it

IIF([The Field] like "*'*",Replace([The field],"'",""),[The Field])

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Klatuu]

Use a calculated field to return the number without the apostrophe if it has
one:

MyNumber: IIf([NumberField] Is Null, Null, Clng(Relace([NumberField], "'",
""))

 

[fredg]

I know this maybe very simple, but I can't seem to get my formulat to work.
I need to remove the leading apostrophe in a numeric field from a text file.
When I use the replace function I get an "ERROR# in fields without data.

How can i strip away the apostrophe from these numbers?

'100334613071008899

I think there is a function I can use but I don't know which one it is.

Thanks in advance.

NoQuote:IIf(Not IsNull([FieldName]),Replace([FieldName],"'",""),Null)

 

[John W. Vinson]

I know this maybe very simple, but I can't seem to get my formulat to work.
I need to remove the leading apostrophe in a numeric field from a text file.
When I use the replace function I get an "ERROR# in fields without data.

How can i strip away the apostrophe from these numbers?

'100334613071008899

I think there is a function I can use but I don't know which one it is.

Thanks in advance.

Are you going to be doing arithmatic with this field? If not, use a Text
datatype rather than a Number. A Long Integer (the default size) number is
limited to 2 billion and change; a Double Float to about 14 decimal places;
you could use a Decimal number datatype (28 digits), but if this is a part
number or identifier of some sort, and you won't be doing calculations with
it, a Text datatype would be much easier to manage.

John W. Vinson [MVP]

 

[fredg]

Are you going to be doing arithmatic with this field? If not, use a Text
datatype rather than a Number. A Long Integer (the default size) number is
limited to 2 billion and change; a Double Float to about 14 decimal places;
you could use a Decimal number datatype (28 digits), but if this is a part
number or identifier of some sort, and you won't be doing calculations with
it, a Text datatype would be much easier to manage.

John W. Vinson [MVP]

John,
I believe he means the field has numbers in it, not that the datatype
is Number.
The field datatype must already be text as the user would not be able
to enter the apostrophe were it in fact a Number datatype.