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.

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

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
..

"Natalie" <Natalie@discussions.microsoft.com> wrote in message 
news:7C32AEFF-337A-4C21-97CA-12EAD7F3D387@microsoft.com...
>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.
>
> 

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

MyNumber: IIf([NumberField] Is Null, Null, Clng(Relace([NumberField], "'", 
""))
-- 
Dave Hargis, Microsoft Access MVP


"Natalie" wrote:

> 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.
> 
> 

On Mon, 4 Feb 2008 08:07:00 -0800, Natalie wrote:

> 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)

-- 
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

On Mon, 4 Feb 2008 08:07:00 -0800, Natalie <Natalie@discussions.microsoft.com>
wrote:

>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]

On Mon, 04 Feb 2008 10:42:33 -0700, John W. Vinson wrote:

> On Mon, 4 Feb 2008 08:07:00 -0800, Natalie <Natalie@discussions.microsoft.com>
> wrote:
> 
>>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]

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.

-- 
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail