Clipping words short in cells

  • Thread starter Thread starter BTU_needs_assistance_43
  • Start date Start date
B

BTU_needs_assistance_43

I have a program thats bringing in lots of numbers related to shots which are
broken down by date. All the shots in the same area that are done on
different dates have the same name and are "numbered" off alphebetically at
the end of the name (ex:oww33a, oww33b, oww33c...) There are a number of rows
for each named shot. What is done now is all the cells are brought into a
table and listed. What I want to do additionally is be able to list the
totals of everything summed up (simple) but under the prefix of just "oww33"
including all shots in the area into one. This will make it alot easier to
link my tables in queries later. Is there any way to go about this?
 
So are you saying that the Left([ShotName],5) is what you wish to compare?
If so, use a query and add that as a new field.

(and by the way, you can use a query as a source in another query ... you
aren't limited to using just tables ...)

But I'm not clear on how many tables you will be trying to link ... "how"
depends on "what", and I can't visual what data structure you're using...

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
OK I'll try. I have at this point 3 tables I am using to bring in all the
information I need from these reports. There are fields of data breaking down
various volumes and whatnot for each shot. This is more or less how it
looks...

owe23a st1 #####
owe23a st2 #####
owe23b st3 #####
owe23b st4 #####
owe23c st5 #####
owe23c st6 #####

I want to be able to just take the owe23 and drop the a, b, and c off the
end so that I can use just the owe23 for all totals. I have found a function
(Len()) that will count the number of letters in a word or cell and then
output a number but you can also subtract off of that number in the function.
I am wondering if I can output that again to get the original word -1 letter.
Or maybe I'm crazy and thats not possible. The letters I'm bringing in from
report to report can vary so I need to write the program such that it will
bring in the letters no matter what they are and just drop the end letter.

Jeff Boyce said:
So are you saying that the Left([ShotName],5) is what you wish to compare?
If so, use a query and add that as a new field.

(and by the way, you can use a query as a source in another query ... you
aren't limited to using just tables ...)

But I'm not clear on how many tables you will be trying to link ... "how"
depends on "what", and I can't visual what data structure you're using...

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

BTU_needs_assistance_43 said:
I have a program thats bringing in lots of numbers related to shots which
are
broken down by date. All the shots in the same area that are done on
different dates have the same name and are "numbered" off alphebetically
at
the end of the name (ex:oww33a, oww33b, oww33c...) There are a number of
rows
for each named shot. What is done now is all the cells are brought into a
table and listed. What I want to do additionally is be able to list the
totals of everything summed up (simple) but under the prefix of just
"oww33"
including all shots in the area into one. This will make it alot easier to
link my tables in queries later. Is there any way to go about this?
Click to expand...
Click to expand...
 
Take another look at my response. Use the Left() function to get the
leftmost 5 characters. This assumes your code is always 5 characters, plus
the alphabetic 6th.

Regards

Jeff Boyce
Microsoft Office/Access MVP

BTU_needs_assistance_43 said:
OK I'll try. I have at this point 3 tables I am using to bring in all the
information I need from these reports. There are fields of data breaking
down
various volumes and whatnot for each shot. This is more or less how it
looks...

owe23a st1 #####
owe23a st2 #####
owe23b st3 #####
owe23b st4 #####
owe23c st5 #####
owe23c st6 #####

I want to be able to just take the owe23 and drop the a, b, and c off the
end so that I can use just the owe23 for all totals. I have found a
function
(Len()) that will count the number of letters in a word or cell and then
output a number but you can also subtract off of that number in the
function.
I am wondering if I can output that again to get the original word -1
letter.
Or maybe I'm crazy and thats not possible. The letters I'm bringing in
from
report to report can vary so I need to write the program such that it will
bring in the letters no matter what they are and just drop the end letter.

Jeff Boyce said:
So are you saying that the Left([ShotName],5) is what you wish to
compare?
If so, use a query and add that as a new field.

(and by the way, you can use a query as a source in another query ... you
aren't limited to using just tables ...)

But I'm not clear on how many tables you will be trying to link ... "how"
depends on "what", and I can't visual what data structure you're using...

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

"BTU_needs_assistance_43"
<[email protected]>
wrote in message
I have a program thats bringing in lots of numbers related to shots
which
are
broken down by date. All the shots in the same area that are done on
different dates have the same name and are "numbered" off
alphebetically
at
the end of the name (ex:oww33a, oww33b, oww33c...) There are a number
of
rows
for each named shot. What is done now is all the cells are brought into
a
table and listed. What I want to do additionally is be able to list the
totals of everything summed up (simple) but under the prefix of just
"oww33"
including all shots in the area into one. This will make it alot easier
to
link my tables in queries later. Is there any way to go about this?
Click to expand...
Click to expand...
Click to expand...
 
They aren't always 5 Characters but I think I see how to combine your example
with mine so that it will count the number of letters in the cell and then
use yours to cut out that last one! Thank you very much!

Jeff Boyce said:
Take another look at my response. Use the Left() function to get the
leftmost 5 characters. This assumes your code is always 5 characters, plus
the alphabetic 6th.

Regards

Jeff Boyce
Microsoft Office/Access MVP

BTU_needs_assistance_43 said:
OK I'll try. I have at this point 3 tables I am using to bring in all the
information I need from these reports. There are fields of data breaking
down
various volumes and whatnot for each shot. This is more or less how it
looks...

owe23a st1 #####
owe23a st2 #####
owe23b st3 #####
owe23b st4 #####
owe23c st5 #####
owe23c st6 #####

I want to be able to just take the owe23 and drop the a, b, and c off the
end so that I can use just the owe23 for all totals. I have found a
function
(Len()) that will count the number of letters in a word or cell and then
output a number but you can also subtract off of that number in the
function.
I am wondering if I can output that again to get the original word -1
letter.
Or maybe I'm crazy and thats not possible. The letters I'm bringing in
from
report to report can vary so I need to write the program such that it will
bring in the letters no matter what they are and just drop the end letter.

Jeff Boyce said:
So are you saying that the Left([ShotName],5) is what you wish to
compare?
If so, use a query and add that as a new field.

(and by the way, you can use a query as a source in another query ... you
aren't limited to using just tables ...)

But I'm not clear on how many tables you will be trying to link ... "how"
depends on "what", and I can't visual what data structure you're using...

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

"BTU_needs_assistance_43"
<[email protected]>
wrote in message
I have a program thats bringing in lots of numbers related to shots
which
are
broken down by date. All the shots in the same area that are done on
different dates have the same name and are "numbered" off
alphebetically
at
the end of the name (ex:oww33a, oww33b, oww33c...) There are a number
of
rows
for each named shot. What is done now is all the cells are brought into
a
table and listed. What I want to do additionally is be able to list the
totals of everything summed up (simple) but under the prefix of just
"oww33"
including all shots in the area into one. This will make it alot easier
to
link my tables in queries later. Is there any way to go about this?
Click to expand...
Click to expand...
Click to expand...
 
I have found a function
(Len()) that will count the number of letters in a word or cell and then
output a number but you can also subtract off of that number in the function.
I am wondering if I can output that again to get the original word -1 letter.
Click to expand...

Left([fieldname], Len([fieldname]) - 1)
 
Check Left(), Mid(), Right() and/or Len() functions. It sounds like some
combination will do the job for you.

Regards

Jeff Boyce
Microsoft Office/Access MVP

BTU_needs_assistance_43 said:
They aren't always 5 Characters but I think I see how to combine your
example
with mine so that it will count the number of letters in the cell and then
use yours to cut out that last one! Thank you very much!

Jeff Boyce said:
Take another look at my response. Use the Left() function to get the
leftmost 5 characters. This assumes your code is always 5 characters,
plus
the alphabetic 6th.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"BTU_needs_assistance_43"
<[email protected]>
wrote in message
OK I'll try. I have at this point 3 tables I am using to bring in all
the
information I need from these reports. There are fields of data
breaking
down
various volumes and whatnot for each shot. This is more or less how it
looks...

owe23a st1 #####
owe23a st2 #####
owe23b st3 #####
owe23b st4 #####
owe23c st5 #####
owe23c st6 #####

I want to be able to just take the owe23 and drop the a, b, and c off
the
end so that I can use just the owe23 for all totals. I have found a
function
(Len()) that will count the number of letters in a word or cell and
then
output a number but you can also subtract off of that number in the
function.
I am wondering if I can output that again to get the original word -1
letter.
Or maybe I'm crazy and thats not possible. The letters I'm bringing in
from
report to report can vary so I need to write the program such that it
will
bring in the letters no matter what they are and just drop the end
letter.

:

So are you saying that the Left([ShotName],5) is what you wish to
compare?
If so, use a query and add that as a new field.

(and by the way, you can use a query as a source in another query ...
you
aren't limited to using just tables ...)

But I'm not clear on how many tables you will be trying to link ...
"how"
depends on "what", and I can't visual what data structure you're
using...

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

"BTU_needs_assistance_43"
<[email protected]>
wrote in message
I have a program thats bringing in lots of numbers related to shots
which
are
broken down by date. All the shots in the same area that are done on
different dates have the same name and are "numbered" off
alphebetically
at
the end of the name (ex:oww33a, oww33b, oww33c...) There are a
number
of
rows
for each named shot. What is done now is all the cells are brought
into
a
table and listed. What I want to do additionally is be able to list
the
totals of everything summed up (simple) but under the prefix of just
"oww33"
including all shots in the area into one. This will make it alot
easier
to
link my tables in queries later. Is there any way to go about this?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top