Results 1 to 6 of 6
  1. #1
    eskybel is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    May 2012
    Posts
    74

    Remove first 5 characters from field, display the rest?


    Hello all,

    I have a report which displays a number of fields from a table. One of the fields is a delivery address, of which the first line is a dock number (##-##), then the rest is the standard address. What I need to do is find a way to have my report display this field without the first 5 characters.

    I am using =right([field],2) in another part of the report to display only the last two characters in the field, and this works great.

    Now I need another field to display everything but the first 5 characters, and have been having trouble finding the correct expression. Since the addresses are not fixed length, I cannot use a fixed value to pull everything up to the first 5.

    Is there an easy command for this?

    TIA,
    Adam

  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,530
    Try:
    =Right([Field],Len([Field])-5)
    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
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Or Mid([Field], 6)
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    eskybel is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    May 2012
    Posts
    74
    Both worked great! Thanks!

  5. #5
    eskybel is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    May 2012
    Posts
    74
    Here's another one for you:
    My label is querying information from our SQL database. The addresses in SQL DB have carriage returns in them, and when you look at the address in an Access table, the address displays a "?" in a little square box for the carriage returns. When I go to print the label, these "?" boxes show up, which is not desired. If I copy and paste this anywhere else, the carriage returns work, no ? is displayed. But, in Access, it shows the ? and no carriage return happens.

    Is there a way to replace the ? with an actual carriage return, or somehow work a "replace" with carriage return so the address displays properly?

  6. #6
    eskybel is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    May 2012
    Posts
    74
    Found the answer in an unlikely place, but here it is:
    =Replace([FIELD],Chr(10),Chr(13) & Chr(10))

    Seems to have worked!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Wierd Characters in Field
    By RegVaz in forum Access
    Replies: 5
    Last Post: 01-17-2012, 01:46 PM
  2. Grab first characters from field
    By sau3-access in forum Access
    Replies: 1
    Last Post: 10-04-2011, 10:40 AM
  3. Help with subforms (and the rest)
    By Franco27 in forum Reports
    Replies: 0
    Last Post: 03-14-2011, 09:43 AM
  4. Replies: 5
    Last Post: 03-10-2011, 02:19 PM
  5. Replies: 5
    Last Post: 01-28-2010, 09:10 AM

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