obtaining percentage from text

  • Thread starter Thread starter ChuckW
  • Start date Start date
C

ChuckW

Hi,

I have a field that has percentage values as part of
text. For instance, it will John Smith 10% Commission or
Joe Jones 15% Commission. What I need to do is to
isolate the percentage and use this as a calculated
value. I would then multiply a sale value from another
field by .1 for John Smith and .15 for Joe Jones. Is
there a way to run a query or queries that go from Joe
Jones 15% Commission to .15 and John Smith 10% Commission
to .1?

Thanks,

Chuck
 
Dear Chuck:

You need to work toward a method that will work with both all (or as
many as possible) of the existing data as well as any likely future
data.

When I look at this, it looks like you need to find a string of
consecutive digits (perhaps with a decimal point embedded) that
preceeds a % in the string. The first thing I'd do is look at the
data. Find the exceptions. Are there any examples of this text
column that do not contain a "%"? Next, how complex of a value
preceeds the "%"? Is it always 2 digits? Never 1 or 3? Never has a
decimal point?

SELECT YourColumn FROM YourTable
WHERE YourColumn NOT LIKE "*[0-9][0-9]%*"

Substitute the name of YourColumn and of YourTable in the above. It
will tell you if there are any exceptions to the two-digit then %
format in your table.

What you do next may depend on what you see from this. Find the
excpetions and decide what you want to do with them. Program them.
Come back here for more information. OK?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Tom,

Thanks for your help. Everything is two digits. In fact
everything is either 10% or 15%. The data source is
Quickbooks. I have a program that takes data out of QB
and into Access. The problem is that I cannot change the
field in Quicbooks. The accounting people need all of
the extra text beyond the 10% or 15%. I need to isolate
the percent and then change the field from text to number
I think. Any thoughts?

Thanks,

Chuck
-----Original Message-----
Dear Chuck:

You need to work toward a method that will work with both all (or as
many as possible) of the existing data as well as any likely future
data.

When I look at this, it looks like you need to find a string of
consecutive digits (perhaps with a decimal point embedded) that
preceeds a % in the string. The first thing I'd do is look at the
data. Find the exceptions. Are there any examples of this text
column that do not contain a "%"? Next, how complex of a value
preceeds the "%"? Is it always 2 digits? Never 1 or 3? Never has a
decimal point?

SELECT YourColumn FROM YourTable
WHERE YourColumn NOT LIKE "*[0-9][0-9]%*"

Substitute the name of YourColumn and of YourTable in the above. It
will tell you if there are any exceptions to the two- digit then %
format in your table.

What you do next may depend on what you see from this. Find the
excpetions and decide what you want to do with them. Program them.
Come back here for more information. OK?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hi,

I have a field that has percentage values as part of
text. For instance, it will John Smith 10% Commission or
Joe Jones 15% Commission. What I need to do is to
isolate the percentage and use this as a calculated
value. I would then multiply a sale value from another
field by .1 for John Smith and .15 for Joe Jones. Is
there a way to run a query or queries that go from Joe
Jones 15% Commission to .15 and John Smith 10% Commission
to .1?

Thanks,

Chuck
Click to expand...

.
Click to expand...
 
Dear Chuck:

To be very simple, how about this:

IIf([YourColumn] LIKE "*10%*", .1, .15)

That means than all the rows where the string contains "10%" will come
in at .1 and all the other rows will come in at .15. You must change
"YourColumn" to the actual name of the column containing this text.

You set this up as an additional, calculated column in the query.

Let me know how you make out with this. I recommend that, once you
have it working, place this calculated column in a query right next to
that text column, and spend a few minutes reading the input and
checking the output, to see if there are exceptions to the rule you've
said is there.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Tom,

Thanks for your help. Everything is two digits. In fact
everything is either 10% or 15%. The data source is
Quickbooks. I have a program that takes data out of QB
and into Access. The problem is that I cannot change the
field in Quicbooks. The accounting people need all of
the extra text beyond the 10% or 15%. I need to isolate
the percent and then change the field from text to number
I think. Any thoughts?

Thanks,

Chuck
-----Original Message-----
Dear Chuck:

You need to work toward a method that will work with both all (or as
many as possible) of the existing data as well as any likely future
data.

When I look at this, it looks like you need to find a string of
consecutive digits (perhaps with a decimal point embedded) that
preceeds a % in the string. The first thing I'd do is look at the
data. Find the exceptions. Are there any examples of this text
column that do not contain a "%"? Next, how complex of a value
preceeds the "%"? Is it always 2 digits? Never 1 or 3? Never has a
decimal point?

SELECT YourColumn FROM YourTable
WHERE YourColumn NOT LIKE "*[0-9][0-9]%*"

Substitute the name of YourColumn and of YourTable in the above. It
will tell you if there are any exceptions to the two- digit then %
format in your table.

What you do next may depend on what you see from this. Find the
excpetions and decide what you want to do with them. Program them.
Come back here for more information. OK?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hi,

I have a field that has percentage values as part of
text. For instance, it will John Smith 10% Commission or
Joe Jones 15% Commission. What I need to do is to
isolate the percentage and use this as a calculated
value. I would then multiply a sale value from another
field by .1 for John Smith and .15 for Joe Jones. Is
there a way to run a query or queries that go from Joe
Jones 15% Commission to .15 and John Smith 10% Commission
to .1?

Thanks,

Chuck
Click to expand...

.
Click to expand...
Click to expand...
 
Tom,

Thanks again for your help. Your suggestion worked like
a charm.

Chuck
-----Original Message-----
Dear Chuck:

To be very simple, how about this:

IIf([YourColumn] LIKE "*10%*", .1, .15)

That means than all the rows where the string contains "10%" will come
in at .1 and all the other rows will come in at .15. You must change
"YourColumn" to the actual name of the column containing this text.

You set this up as an additional, calculated column in the query.

Let me know how you make out with this. I recommend that, once you
have it working, place this calculated column in a query right next to
that text column, and spend a few minutes reading the input and
checking the output, to see if there are exceptions to the rule you've
said is there.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Tom,

Thanks for your help. Everything is two digits. In fact
everything is either 10% or 15%. The data source is
Quickbooks. I have a program that takes data out of QB
and into Access. The problem is that I cannot change the
field in Quicbooks. The accounting people need all of
the extra text beyond the 10% or 15%. I need to isolate
the percent and then change the field from text to number
I think. Any thoughts?

Thanks,

Chuck
-----Original Message-----
Dear Chuck:

You need to work toward a method that will work with both all (or as
many as possible) of the existing data as well as any likely future
data.

When I look at this, it looks like you need to find a string of
consecutive digits (perhaps with a decimal point embedded) that
preceeds a % in the string. The first thing I'd do is look at the
data. Find the exceptions. Are there any examples of this text
column that do not contain a "%"? Next, how complex
Click to expand...
of
a value
preceeds the "%"? Is it always 2 digits? Never 1 or 3? Never has a
decimal point?

SELECT YourColumn FROM YourTable
WHERE YourColumn NOT LIKE "*[0-9][0-9]%*"

Substitute the name of YourColumn and of YourTable in the above. It
will tell you if there are any exceptions to the two- digit then %
format in your table.

What you do next may depend on what you see from
Click to expand...
this.
Find the
excpetions and decide what you want to do with them. Program them.
Come back here for more information. OK?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Thu, 12 Aug 2004 07:54:35 -0700, "ChuckW"

Hi,

I have a field that has percentage values as part of
text. For instance, it will John Smith 10%
Click to expand...
Commission
or
Joe Jones 15% Commission. What I need to do is to
isolate the percentage and use this as a calculated
value. I would then multiply a sale value from another
field by .1 for John Smith and .15 for Joe Jones. Is
there a way to run a query or queries that go from Joe
Jones 15% Commission to .15 and John Smith 10% Commission
to .1?

Thanks,

Chuck



.
Click to expand...
Click to expand...

.
Click to expand...
 
Back
Top