Results 1 to 4 of 4
  1. #1
    techexpressinc's Avatar
    techexpressinc is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Dec 2008
    Location
    Indiana, USA
    Posts
    151

    Replace Function ? - how to replace the a 4th position null with a dash

    I have a field that I need to replace the 4 character in with a dash when the position is null.
    Sample of input values:
    ABC
    GATE


    HAV
    ROAD
    Output values wanted:
    ABC-
    GATE
    HAV-
    ROAD

    I have tried this and variations without success:
    Outputfield: Replace([Inputfield],"","-",[4,[1]])

    Any suggestions?
    Thanks Russ

  2. #2
    techexpressinc's Avatar
    techexpressinc is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Dec 2008
    Location
    Indiana, USA
    Posts
    151
    Solved =
    CompanyID4: IIf(Len([CompanyID4L])=3,[CompanyID4L] & "-",[CompanyID4L])

  3. #3
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Hi,

    Is there a possibility of having values such as below ?

    AB
    GATES
    HAV Z

    Edit : We both posted at the same time.
    Thanks for sharing the solution.

    Thanks

  4. #4
    techexpressinc's Avatar
    techexpressinc is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Dec 2008
    Location
    Indiana, USA
    Posts
    151
    The length of the field was 4 characters in. And there were at least 3 characters to start with. I was matching up plan-ids from two systems. One just worked with the first 3 characters. The other had a hypen and then a number i.e. ABC-001 and ABC-002, etc.

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

Similar Threads

  1. Replies: 2
    Last Post: 03-03-2012, 10:16 AM
  2. Find and Replace
    By Bedsingar in forum Access
    Replies: 1
    Last Post: 08-14-2011, 01:10 PM
  3. Replace Function with Table Lookup
    By smurof in forum Access
    Replies: 1
    Last Post: 07-29-2011, 07:52 PM
  4. auto replace??
    By d3pl0y3d in forum Access
    Replies: 2
    Last Post: 02-11-2011, 02:09 PM
  5. Applying a find/replace function
    By Arr in forum Programming
    Replies: 2
    Last Post: 10-12-2009, 12:28 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