Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Paintballlovr is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2013
    Posts
    96

    Removing blank spaces created by LEFT statement

    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?

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,610
    What was the expression you tried that involved the use of trim.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    Paintballlovr is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2013
    Posts
    96
    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.

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows XP Access 2003
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,610
    Just a guess, but try:
    Expr1: Trim(Left([EFT Payors]![Payer],14)) & "*"
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  5. #5
    Paintballlovr is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2013
    Posts
    96
    still have the spaces between the last letter or number and the *

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    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.

  7. #7
    Paintballlovr is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2013
    Posts
    96
    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.

  8. #8
    Paintballlovr is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2013
    Posts
    96
    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.

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    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

  10. #10
    Paintballlovr is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2013
    Posts
    96
    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.

  11. #11
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    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.

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    If you want to remove all spaces then count 14 from left, try:

    Left(Replace([fieldname]," ", ""), 14) & "*"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    Paintballlovr is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2013
    Posts
    96
    Quote Originally Posted by orange View Post
    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.

  14. #14
    Paintballlovr is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2013
    Posts
    96
    Quote Originally Posted by June7 View Post
    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?

  15. #15
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    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

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 04-16-2013, 07:57 PM
  2. Remove blank spaces after strings in fields
    By Modify_inc in forum Access
    Replies: 8
    Last Post: 08-18-2012, 06:30 PM
  3. Replies: 1
    Last Post: 07-25-2011, 09:57 AM
  4. Replies: 0
    Last Post: 03-29-2011, 10:38 AM
  5. Blank spaces at start of entries
    By rcmglover in forum Access
    Replies: 2
    Last Post: 03-26-2010, 10:42 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums