How to extract numeric portion of alphanumeric data

[Guest]

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

 

[Tom Lake]

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

 

[Tom Lake]

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

 

[Marshall Barton]

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

 

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

 

[Guest]

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?

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

 

[Guest]

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

 

[Guest]

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?

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

 

[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?

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

 

[Guest]

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.

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?

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