hello .. 
I have a text column in my table. This text is a explanation of accounting process. But our database administrator made a mistake.
we dont have an account number column. instead, in explanation we have account-customer Id- number in text explanation. it always
starts with 00158 or with 58939.. now my question is: how can i get 13 digit number which begins with these numbers.. 
which functions should I use?
thanks

Hi

You'll need to use some combination of:

    InStr - To find the numbers you're looking form.
    Mid - To extract the number from within the text.
    Clng - To convert the text to a number.

Lookm them up in help to get the exact details.

Brian
>  from:    yesil kalem <yesilkalem@h...>
>  date:    Thu, 20 Jun 2002 09:22:29
>  to:      access@p...
>  subject: Re: [access] select a phrase in a text column
> 
> hello .. 
> I have a text column in my table. This text is a explanation of accounting process. But our database administrator made a
mistake. we dont have an account number column. instead, in explanation we have account-customer Id- number in text explanation. it
always starts with 00158 or with 58939.. now my question is: how can i get 13 digit number which begins with these numbers.. 
> which functions should I use?
> thanks

Hi!

First, you don't specify if those numbers (13 digits) are in a fixed position 
or just in the midlle of the field. That of course would change A LOT the 
problem. If this is the case, I have developed a function that returns you the 
nth numeric token (that's to say, the nth group of contiguous digit chars) 
within a string. If that's the situation, just let me know and I can send you 
the code. This requires that the digits you are looking for are ALWAYS in the 
same "order", at least, if not position (offset).

Second... tell your database administrator TO ADD that column *RIGHT NOW*, and 
to fix the problem for the future!!!! A mistake is OK, perseverating is NOT!

   Claudio de Biasio
    Team 97 S.r.l.



Quoting yesil kalem <yesilkalem@h...>:

> hello .. 
> I have a text column in my table. This text is a explanation of
> accounting process. But our database administrator made a mistake. we
> dont have an account number column. instead, in explanation we have
> account-customer Id- number in text explanation. it always starts with
> 00158 or with 58939.. now my question is: how can i get 13 digit number
> which begins with these numbers.. 
> which functions should I use?
> thanks
> 

<< You'll need to use some combination of:

    InStr - To find the numbers you're looking form.
    Mid - To extract the number from within the text.
    Clng - To convert the text to a number. >>

This is good except for one thing.  An account number is similar to a ZIP 
code or a social security number in that you don't do math on it.  
Therefore, you wouldn't convert the account number from text to number.  
You'd leave it as a text field.

The rule of thumb is that if you don't do math on it, it's text.  If you 
do math on it, convert it to a number or a date.

Greg

13 digit number is not fixed. sometimes at the beginning or end or sometimes
at the and..
----- Original Message -----
From: <cdebiasio@t...>
To: "Access" <access@p...>
Sent: Thursday, June 20, 2002 1:16 PM
Subject: [access] Re: select a phrase in a text column


> Hi!
>
> First, you don't specify if those numbers (13 digits) are in a fixed
position
> or just in the midlle of the field. That of course would change A LOT the
> problem. If this is the case, I have developed a function that returns you
the
> nth numeric token (that's to say, the nth group of contiguous digit chars)
> within a string. If that's the situation, just let me know and I can send
you
> the code. This requires that the digits you are looking for are ALWAYS in
the
> same "order", at least, if not position (offset).
>
> Second... tell your database administrator TO ADD that column *RIGHT NOW*,
and
> to fix the problem for the future!!!! A mistake is OK, perseverating is
NOT!
>
>    Claudio de Biasio
>     Team 97 S.r.l.
>
>
>
> Quoting yesil kalem <yesilkalem@h...>:
>
> > hello ..
> > I have a text column in my table. This text is a explanation of
> > accounting process. But our database administrator made a mistake. we
> > dont have an account number column. instead, in explanation we have
> > account-customer Id- number in text explanation. it always starts with
> > 00158 or with 58939.. now my question is: how can i get 13 digit number
> > which begins with these numbers..
> > which functions should I use?
> > thanks
> >
>
>

Is the account-customerID string a fixed width, in other words, is it
always 8 characters, or 10 characters

John Ruff - The Eternal Optimist :-)
Always Looking for a Contract Opportunity

www.noclassroom.com
xxx.xxx.xxxx
9306 Farwest Dr SW
Lakewood, WA 98498 



-----Original Message-----
From: yesilkalem [mailto:yesilkalem@h...] 
Sent: Thursday, June 20, 2002 7:15 AM
To: Access
Subject: [access] Re: select a phrase in a text column


13 digit number is not fixed. sometimes at the beginning or end or
sometimes at the and..
----- Original Message -----
From: <cdebiasio@t...>
To: "Access" <access@p...>
Sent: Thursday, June 20, 2002 1:16 PM
Subject: [access] Re: select a phrase in a text column


> Hi!
>
> First, you don't specify if those numbers (13 digits) are in a fixed
position
> or just in the midlle of the field. That of course would change A LOT 
> the problem. If this is the case, I have developed a function that 
> returns you
the
> nth numeric token (that's to say, the nth group of contiguous digit 
> chars) within a string. If that's the situation, just let me know and 
> I can send
you
> the code. This requires that the digits you are looking for are ALWAYS

> in
the
> same "order", at least, if not position (offset).
>
> Second... tell your database administrator TO ADD that column *RIGHT 
> NOW*,
and
> to fix the problem for the future!!!! A mistake is OK, perseverating 
> is
NOT!
>
>    Claudio de Biasio
>     Team 97 S.r.l.
>
>
>
> Quoting yesil kalem <yesilkalem@h...>:
>
> > hello ..
> > I have a text column in my table. This text is a explanation of 
> > accounting process. But our database administrator made a mistake. 
> > we dont have an account number column. instead, in explanation we 
> > have account-customer Id- number in text explanation. it always 
> > starts with 00158 or with 58939.. now my question is: how can i get 
> > 13 digit number which begins with these numbers.. which functions 
> > should I use? thanks
> >
>
> ---
> Change your mail options at http://p2p.wrox.com/manager.asp or to 
> unsubscribe send a blank email to 
>

Can you at least grant is the first "digits sequence"? (Or the second, or the 
third, or the only one)? Or, is this sequence preceeded by a fixed string?

Unless it is so, there's no solution to your problem.

E.g. let's suppose you want to figure out the "Customer reference" scanning 
these lines:

1) "Invoice 12943 by Customer 9399443 totals 23493 USD"
2) "Cust. 23499 totals US$ 23923 in invoice 29394"
3) "Invoice 23929, totalizing 99349 USD, can be referred to order 929249"

1) and 2) have "Customer" or "Cust." preceeding the Customer ID... that could 
be a criteria. The position changes, so that's NOT a criteria. 3) doesn't even 
have a Customer reference, but, how you can tell?

What I mean is, not always you can find a solution to a "problem". If I show 
you the number 12 and ask you to tell me if it comes from 1x12, 2x6 or 3x4, you 
simply cannot tell. Nor can a computer. This is your situation, or almost. If 
you combine information improperly, you can end up not having INFORMATION, but 
BYTES. Which is NOT the same thing.

Unless you send me some samples of what you have, I cannot tell you much more 
than this...

   Claudio de Biasio
     Team 97 S.r.l.


Quoting yesilkalem <yesilkalem@h...>:

> 13 digit number is not fixed. sometimes at the beginning or end or
> sometimes
> at the and..
> ----- Original Message -----
> From: <cdebiasio@t...>
> To: "Access" <access@p...>
> Sent: Thursday, June 20, 2002 1:16 PM
> Subject: [access] Re: select a phrase in a text column
> 
> 
> > Hi!
> >
> > First, you don't specify if those numbers (13 digits) are in a fixed
> position
> > or just in the midlle of the field. That of course would change A LOT
> the
> > problem. If this is the case, I have developed a function that returns
> you
> the
> > nth numeric token (that's to say, the nth group of contiguous digit
> chars)
> > within a string. If that's the situation, just let me know and I can
> send
> you
> > the code. This requires that the digits you are looking for are ALWAYS
> in
> the
> > same "order", at least, if not position (offset).
> >
> > Second... tell your database administrator TO ADD that column *RIGHT
> NOW*,
> and
> > to fix the problem for the future!!!! A mistake is OK, perseverating
> is
> NOT!
> >
> >    Claudio de Biasio
> >     Team 97 S.r.l.
> >
> >
> >
> > Quoting yesil kalem <yesilkalem@h...>:
> >
> > > hello ..
> > > I have a text column in my table. This text is a explanation of
> > > accounting process. But our database administrator made a mistake.
> we
> > > dont have an account number column. instead, in explanation we have
> > > account-customer Id- number in text explanation. it always starts
> with
> > > 00158 or with 58939.. now my question is: how can i get 13 digit
> number
> > > which begins with these numbers..
> > > which functions should I use?
> > > thanks
> > >
> >
> >
> 
> 

That is unless you want it to sort in numeric order and the account numbers
contain a different number of significant digits, then maybe you would want
to use a long or integer value becuase strings won't sort correctly unless
they are 0 padded.

|-----Original Message-----
|From: Gregory Serrano [mailto:SerranoG@m...]
|Sent: Thursday, June 20, 2002 2:10 PM
|To: Access
|Subject: [access] Re: select a phrase in a text column
|
|
|<< You'll need to use some combination of:
|
|    InStr - To find the numbers you're looking form.
|    Mid - To extract the number from within the text.
|    Clng - To convert the text to a number. >>
|
|This is good except for one thing.  An account number is similar to a ZIP
|code or a social security number in that you don't do math on it.
|Therefore, you wouldn't convert the account number from text to number.
|You'd leave it as a text field.
|
|The rule of thumb is that if you don't do math on it, it's text.  If you
|do math on it, convert it to a number or a date.
|
|Greg