trimming from left

[Guest]

I have a field that has letters and numbers leading in front of the client's
name. These need to go. I am having difficulty in the trimming of the
leading 7 letters and numbers and 1 space in front of the name. I would like
to place this in the query grid. Can anyone assist? Thanks.
John

 

[Guest]

If the lenght of the leading letters and numbers is alway 7 plus a space then
it is easy.
Client Name: Right([YourFieldName], Len([YourFieldName]) -8)

 

[Guest]

Karl, thanks. It worked like a charm.
John

If the lenght of the leading letters and numbers is alway 7 plus a space then
it is easy.
Client Name: Right([YourFieldName], Len([YourFieldName]) -8)

I have a field that has letters and numbers leading in front of the client's
name. These need to go. I am having difficulty in the trimming of the
leading 7 letters and numbers and 1 space in front of the name. I would like
to place this in the query grid. Can anyone assist? Thanks.
John

 

[John Vinson]

If the lenght of the leading letters and numbers is alway 7 plus a space then
it is easy.
Client Name: Right([YourFieldName], Len([YourFieldName]) -8)

or a bit more simply, Mid([YourFieldName], 9)

The Mid() function takes three arguments - the string, the start
point, and the end point; but if you leave off the end point, it gives
you the rest of the string, however long it might be.

John W. Vinson[MVP]

 

[Guest]

Thank you both for your feedback. Both methods work. Here is a possibility
regarding the leading code for the client, there are times that it will be
provided and it is at the end of the name. The client name can be of varied
length. How would I trim off the code at the end (right) of the client name?
John

If the lenght of the leading letters and numbers is alway 7 plus a space then
it is easy.
Client Name: Right([YourFieldName], Len([YourFieldName]) -8)

or a bit more simply, Mid([YourFieldName], 9)

The Mid() function takes three arguments - the string, the start
point, and the end point; but if you leave off the end point, it gives
you the rest of the string, however long it might be.

John W. Vinson[MVP]

 

[Tom Lake]

Thank you both for your feedback. Both methods work. Here is a
possibility
regarding the leading code for the client, there are times that it will be
provided and it is at the end of the name. The client name can be of
varied
length. How would I trim off the code at the end (right) of the client
name?
John

RTrim([ClientName])

Tom Lake.

 

[John Vinson]

Thank you both for your feedback. Both methods work. Here is a possibility
regarding the leading code for the client, there are times that it will be
provided and it is at the end of the name. The client name can be of varied
length. How would I trim off the code at the end (right) of the client name?

Left([ClientName], Len([ClientName] - 8))

But how can you tell which end needs trimming? Is there any way that
you can get this composite field as two separate fields in the import
data... or at least get it consistant!?


John W. Vinson[MVP]

 

[John Vinson]

RTrim([ClientName])

Tom, won't that just trim trailing *blanks*?

John W. Vinson[MVP]

 

[Guest]

I wish! But the info comes from other "vendors" or sometimes our competitors
and we took the client away from them. Believe me, we have asked for it
broken apart but the response is we have it. It's not so bad when a client
has only a few locations. It's the client that has thousands of location
nationwide. Those are the ones that trimming info is needed the most.
Sometimes what is getting trimmed off is information that we do not need for
location structures as it could throw the reporting process off when the we
or the client try and do a drill down of locations.

Thanks for the response.

John

Thank you both for your feedback. Both methods work. Here is a possibility
regarding the leading code for the client, there are times that it will be
provided and it is at the end of the name. The client name can be of varied
length. How would I trim off the code at the end (right) of the client name?

Left([ClientName], Len([ClientName] - 8))

But how can you tell which end needs trimming? Is there any way that
you can get this composite field as two separate fields in the import
data... or at least get it consistant!?


John W. Vinson[MVP]

 

[Guest]

John V, it is actually;
Left([ClientName], Len([ClientName])-8)
Which is okay as most of the time I like to just get a start and finish
figuring it out on my own. It stays with me better.
John

Thank you both for your feedback. Both methods work. Here is a possibility
regarding the leading code for the client, there are times that it will be
provided and it is at the end of the name. The client name can be of varied
length. How would I trim off the code at the end (right) of the client name?

Left([ClientName], Len([ClientName] - 8))

But how can you tell which end needs trimming? Is there any way that
you can get this composite field as two separate fields in the import
data... or at least get it consistant!?


John W. Vinson[MVP]

 

[John Vinson]

John V, it is actually;
Left([ClientName], Len([ClientName])-8)
Which is okay as most of the time I like to just get a start and finish
figuring it out on my own. It stays with me better.

D'oh!!!

Thank you for accepting my silly mistake so graciously.

John W. Vinson[MVP]