How to extract numeric portion of alphanumeric data

Y

Yamou

I have a number of text strings like these:
[PAYEE_TRANS]
Trans to Nya subproject (Kshs 561,596.30)
Trans to Bon subproject (Kshs 447,406.50)

Is there a way in Access 2002 to extract just the numerical part of these
strings in a query. E.g. IIf(XYZ,numericpartof[PAYEE_TRANS] as
Amount,IIf...))...

The problem is that I find that the transaction type is a subproject
transfer, but the amount of transfer (in our current reporting system) is
only reported as part of the [PAYEE_TRANS] field, not a separate numerical
field like an Amount field. So the challenge is to retrieve the amount from
text such as a above and post them into an [AMOUNT] field.

Is this possible? If so, any suggestions? Thanks
 
T

Tom Lake

Yamou said:
I have a number of text strings like these:
[PAYEE_TRANS]
Trans to Nya subproject (Kshs 561,596.30)
Trans to Bon subproject (Kshs 447,406.50)

Is there a way in Access 2002 to extract just the numerical part of these
strings in a query. E.g. IIf(XYZ,numericpartof[PAYEE_TRANS] as
Amount,IIf...))...

If they're always preceded by Kshs then you can do this:

Val(Mid([PAYEE_TRANS], Instr([PAYEE_TRANS], "Kshs ") + 5))

Tom Lake
 
T

Tom Lake

Tom Lake said:
Yamou said:
I have a number of text strings like these:
[PAYEE_TRANS]
Trans to Nya subproject (Kshs 561,596.30)
Trans to Bon subproject (Kshs 447,406.50)

Is there a way in Access 2002 to extract just the numerical part of these
strings in a query. E.g. IIf(XYZ,numericpartof[PAYEE_TRANS] as
Amount,IIf...))...

If they're always preceded by Kshs then you can do this:

Val(Mid([PAYEE_TRANS], Instr([PAYEE_TRANS], "Kshs ") + 5))

Oops! The comma will stop it. Try this:

Val(Mid(Replace([PAYEE_TRANS], ",", ""), Instr([PAYEE_TRANS], "Kshs ") + 5))

Tom Lake
 
M

Marshall Barton

Tom said:
Yamou said:
I have a number of text strings like these:
[PAYEE_TRANS]
Trans to Nya subproject (Kshs 561,596.30)
Trans to Bon subproject (Kshs 447,406.50)

Is there a way in Access 2002 to extract just the numerical part of these
strings in a query. E.g. IIf(XYZ,numericpartof[PAYEE_TRANS] as
Amount,IIf...))...

If they're always preceded by Kshs then you can do this:

Val(Mid([PAYEE_TRANS], Instr([PAYEE_TRANS], "Kshs ") + 5))


I believe that Val will quit on the comma. CCur will deal
with that, but it can't deal with the trailing paren.

Try something more like:
CCur(Left(Mid(PAYEE_TRANS, Instr(PAYEE_TRANS, "Kshs ") + 5),
Len(Mid(PAYEE_TRANS, Instr(PAYEE_TRANS, "Kshs ") + 5)) - 1))
 
T

Tom Lake

I believe that Val will quit on the comma. CCur will deal
with that, but it can't deal with the trailing paren.

Yup That's why I posted the correction.

Tom Lake

"It's always something. If it's not one thing it's another." - Roseanne
Rosannadanna (Gilda Radner)
 
Y

Yamou

Thanks Tom,

It works perfectly when the string "Kshs " is found; however, there are a
few entries where the typed entries are like "Kshs" or "Ksh" or "Ksh.", etc.
Would you have a sugesstion how to deal with such entries in the formula you
wrote earlier?

Tom Lake said:
Tom Lake said:
Yamou said:
I have a number of text strings like these:
[PAYEE_TRANS]
Trans to Nya subproject (Kshs 561,596.30)
Trans to Bon subproject (Kshs 447,406.50)

Is there a way in Access 2002 to extract just the numerical part of these
strings in a query. E.g. IIf(XYZ,numericpartof[PAYEE_TRANS] as
Amount,IIf...))...

If they're always preceded by Kshs then you can do this:

Val(Mid([PAYEE_TRANS], Instr([PAYEE_TRANS], "Kshs ") + 5))

Oops! The comma will stop it. Try this:

Val(Mid(Replace([PAYEE_TRANS], ",", ""), Instr([PAYEE_TRANS], "Kshs ") + 5))

Tom Lake
 
Y

Yamou

Thanks for your suggestions so far. I was trying to say in my second post
that for all records where the search value = "Kshs " for the InStr function,
the (corrected) formula Tom gave works just fine. Now this string varies to
"Ksh." or "Kshs" or "Kshs.", etc. E.g.:
Transfer Bank to Cash ( Kshs100,000.00 )
Cash transfer - Bank to Cash Ksh.16,000.00

In such cases the formula returns zero. So I was wondering how I could get
around that limitation. Note that all records have at least a "Ksh" string in
the text.

Thanks
 
Y

Yamou

I have tried your suggestions but it returns a type mismatch in a union query
and
query too complex in a select query like this:

SELECT CCur(Left(Mid([PAYEE_TRANS],InStr([PAYEE_TRANS],"Kshs
")+5),Len(Mid([PAYEE_TRANS],InStr([PAYEE_TRANS],"Kshs ")+5))-1))
FROM [Normalize_Transactions (4)];

Not sure what the problem is. Could it be the trailing parenthesis?

Marshall Barton said:
Tom said:
Yamou said:
I have a number of text strings like these:
[PAYEE_TRANS]
Trans to Nya subproject (Kshs 561,596.30)
Trans to Bon subproject (Kshs 447,406.50)

Is there a way in Access 2002 to extract just the numerical part of these
strings in a query. E.g. IIf(XYZ,numericpartof[PAYEE_TRANS] as
Amount,IIf...))...

If they're always preceded by Kshs then you can do this:

Val(Mid([PAYEE_TRANS], Instr([PAYEE_TRANS], "Kshs ") + 5))


I believe that Val will quit on the comma. CCur will deal
with that, but it can't deal with the trailing paren.

Try something more like:
CCur(Left(Mid(PAYEE_TRANS, Instr(PAYEE_TRANS, "Kshs ") + 5),
Len(Mid(PAYEE_TRANS, Instr(PAYEE_TRANS, "Kshs ") + 5)) - 1))
 
M

Marshall Barton

We could probably figure out what the error is, but since it
won't deal with the revised requirements, what's the point.

You really need to figure out the parsing rules that will
deal with all your cases. For example: Optional ending )
preceeded by an arbitrary number of spaces. After trimming
that away, the number might contain a decimal point and some
commas. The number is preceeded by an unspecified non
numeric character, which might be a comma or dot.

Trying to do it in an expression with just what we've seen
so far is going to be so messy that I recommend creating a
VBA function to deal with whatever the rules turn out to be.
--
Marsh
MVP [MS Access]

I have tried your suggestions but it returns a type mismatch in a union query
and
query too complex in a select query like this:

SELECT CCur(Left(Mid([PAYEE_TRANS],InStr([PAYEE_TRANS],"Kshs
")+5),Len(Mid([PAYEE_TRANS],InStr([PAYEE_TRANS],"Kshs ")+5))-1))
FROM [Normalize_Transactions (4)];

Not sure what the problem is. Could it be the trailing parenthesis?

Marshall Barton said:
Tom said:
I have a number of text strings like these:
[PAYEE_TRANS]
Trans to Nya subproject (Kshs 561,596.30)
Trans to Bon subproject (Kshs 447,406.50)

Is there a way in Access 2002 to extract just the numerical part of these
strings in a query. E.g. IIf(XYZ,numericpartof[PAYEE_TRANS] as
Amount,IIf...))...

If they're always preceded by Kshs then you can do this:

Val(Mid([PAYEE_TRANS], Instr([PAYEE_TRANS], "Kshs ") + 5))


I believe that Val will quit on the comma. CCur will deal
with that, but it can't deal with the trailing paren.

Try something more like:
CCur(Left(Mid(PAYEE_TRANS, Instr(PAYEE_TRANS, "Kshs ") + 5),
Len(Mid(PAYEE_TRANS, Instr(PAYEE_TRANS, "Kshs ") + 5)) - 1))
 
Y

Yamou

Thanks,

I played around with Tom's formula and am gettting the results I was hoping
for. So thanks a lot to you all for your support.

Marshall Barton said:
We could probably figure out what the error is, but since it
won't deal with the revised requirements, what's the point.

You really need to figure out the parsing rules that will
deal with all your cases. For example: Optional ending )
preceeded by an arbitrary number of spaces. After trimming
that away, the number might contain a decimal point and some
commas. The number is preceeded by an unspecified non
numeric character, which might be a comma or dot.

Trying to do it in an expression with just what we've seen
so far is going to be so messy that I recommend creating a
VBA function to deal with whatever the rules turn out to be.
--
Marsh
MVP [MS Access]

I have tried your suggestions but it returns a type mismatch in a union query
and
query too complex in a select query like this:

SELECT CCur(Left(Mid([PAYEE_TRANS],InStr([PAYEE_TRANS],"Kshs
")+5),Len(Mid([PAYEE_TRANS],InStr([PAYEE_TRANS],"Kshs ")+5))-1))
FROM [Normalize_Transactions (4)];

Not sure what the problem is. Could it be the trailing parenthesis?

Marshall Barton said:
Tom Lake wrote:
I have a number of text strings like these:
[PAYEE_TRANS]
Trans to Nya subproject (Kshs 561,596.30)
Trans to Bon subproject (Kshs 447,406.50)

Is there a way in Access 2002 to extract just the numerical part of these
strings in a query. E.g. IIf(XYZ,numericpartof[PAYEE_TRANS] as
Amount,IIf...))...

If they're always preceded by Kshs then you can do this:

Val(Mid([PAYEE_TRANS], Instr([PAYEE_TRANS], "Kshs ") + 5))


I believe that Val will quit on the comma. CCur will deal
with that, but it can't deal with the trailing paren.

Try something more like:
CCur(Left(Mid(PAYEE_TRANS, Instr(PAYEE_TRANS, "Kshs ") + 5),
Len(Mid(PAYEE_TRANS, Instr(PAYEE_TRANS, "Kshs ") + 5)) - 1))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top