Removing blank spaces created by LEFT statement

[Paintballlovr]

I have a query where I'm grabbing the first 14 characters of a field and concatenating that with "*". The problem is, some of the fields I'm pulling don't have 14 characters, so I end up with something that looks like "abcde *". Is there a simpler way to do what I'm trying to do or is there a way for me to suppress the blank spaces within my results? I've tried Trim to no success. Here is what my expression looks like:



Expr1: Left([EFT Payors]![Payer],14) & "*"

based off of the example above, I would like the result to be "ABCDE*" instead. Any ideas?

[Bob Fitz]

What was the expression you tried that involved the use of trim.

[Paintballlovr]

I hate to admit, but I've chased my tail around for a bit and can't remember all of the different things I've tried so far. I know I tried Expr1: LTrim(Left([EFT Payors]![Payer],14)) & "*" (also with Rtrim and trim). I did also try to just perform the trim and then adding the concatenate after the fact, but I don't have the code example of that one.

[Bob Fitz]

Just a guess, but try:
Expr1: Trim(Left([EFT Payors]![Payer],14)) & "*"

[Paintballlovr]

still have the spaces between the last letter or number and the *

[orange]

How did you decide to use the leftmost 14 characters, if you know that not all values have 14 characters?

As Bob has mentioned, TRIM will remove leading and/or trailing spaces.

[Paintballlovr]

Right now, 14 is a test length and may change. The reason for it is even though not all fields have 14 characters, I may need that many characters to create a unique value, as some of the values are only different by a couple of characters. When I add len and trim to the expression, my field lengths are 15, so it seems like trim is not being allowed to remove the trailing spaces created by the Left function.

[Paintballlovr]

I wonder if I'm dealing with a data integrity issue of some type. I just created a test table with some fake entries and some from my data and the expression is working. Don't spend anymore brain power for now, let me see if there is some way for me to verify this source table and see if the issue truly lies upstream from this function. Thanks to everyone for their consideration of this issue up to this point. I'll update the thread with my findings.

[orange]

Perhaps you should tell us what you are trying to do in plain English (without any Access/vba functions).
How about telling us more about this.

I may need that many characters to create a unique value

[Paintballlovr]

I have column A, which is a grouping of words and numbers. The grouping length varies and there could be multiple empty spaces between groupings, within the same record (i.e. " 125458 65986"). I want to capture the first 14 characters of each record, remove any trailing blanks, and add a "*" so the result would look like "125458*". I think site settings are going to block the extra spaces in the above example, but there could be a large gap in the column A records. As a side note, I tried blowing the table away and re-importing, but I still run into the same issue.

[orange]

The "*" has special meaning in Access.
You're still talking about How. Can you tell us WHAT the purpose of the 14 characters is in terms of your business. Do you have a single field that is unique?
What is the subject matter of your database -- as general a description as you can.

[June7]

If you want to remove all spaces then count 14 from left, try:

Left(Replace([fieldname]," ", ""), 14) & "*"

[Paintballlovr]

The "*" has special meaning in Access.
You're still talking about How. Can you tell us WHAT the purpose of the 14 characters is in terms of your business. Do you have a single field that is unique?
What is the subject matter of your database -- as general a description as you can.

I'm working with medical billing information, so I can't get specific due to HIPAA, but I think I can build an understanding with generic info. The field I'm trying to trim is the name of the payer on the medical claim, lets say Will's insurance company. When Will's insurance pays on a claim, it includes the company name and a transaction number (Will's insurance company EFT2256587). Since the transaction number will always be different, I want to remove that (the reason for the LEFT 14) and place a * at the end, so I can run a like statement. The reason for that is all of the payers need to be lumped into larger buckets, lets says Will's insurance company is actually a division of Progressive. What I want to do is create a field called "will's insuran*" so everytime a payment comes in down the road, an update query can see that and rename the payer name from Will's insurance company to Progressive. The issue I'm having is for some reason, Will's insurance company doesn't always submit in the same format. One time it may read just like the example above and the next time there are 7 extra spaces between insurance and company. If my left 14 falls within one of these large openings and lets say there ends up being 4 blank spaces between the n and the * (will's insuran(4 blanks)*), then I think my like statement search will miss any entries with 3, 2 or 1 blanks. These will get caught as I'm going to report on any payers that didn't match, but I want to capture as many as possible so there aren't many payers that have to be manually corrected. I am hoping to come up with a solution that when I do my left 14 and there ends up being spaces between the last letter and the *, the blanks will be removed.

[Paintballlovr]

If you want to remove all spaces then count 14 from left, try:

Left(Replace([fieldname]," ", ""), 14) & "*"

This did not work either. I still get results that look like robert(7 blanks)*. Could there be an issue with the formatting of the data in Excel, that needs to be corrected prior to import, that could be causing this?

[orange]

I think you should research Normalization. Below are 2 links that may be helpful to you. You should not be designing a variable length string and appending an asterisk (with the stored value) just so you can use a Like statement.
You need to get your tables designed and normalized, then set up your relationships to match your "Business requirements".

I do not know if Access' security/privacy are adequate for HIPAA - others on the forum may have experience and can advise you accordingly.

Database design / Principles http://forums.aspfree.com/attachment...2&d=1201055452
Entity Relationships http://www.rogersaccesslibrary.com/T...lationship.zip