By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
460,028 Members | 881 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 460,028 IT Pros & Developers. It's quick & easy.

Need to remove point form "-" in a memo field

100+
P: 112
I have a memo field that is supposed to be in paragraph format (with no returns) but half of the records are set up as point form with multiple returns. I have figured out how to remove the returns using an update query with:
Expand|Select|Wrap|Line Numbers
  1. Replace([Description],Chr(13) & Chr(10)," ")
However, I need to remove the “-“ that they used in front of each point first, example:
- This is a problem
- This is a problem
- This is a problem
- And so on…
However this is further complicated by the fact that some of the information in the field has “-“ that I don’t want to remove, for example:
Words that have "-" in them like “on-line” and “e-mail” or number with "-" 1-800-297-8511.
I have been trying to use something like:
Expand|Select|Wrap|Line Numbers
  1. IIF([Description] Like "-*", Replace([Description],"-",""), [Description])
but it replaces all the “-“ not just the ones at the front. Also, there are some that start out without a “-“ on the first line and them have point form below which it completely ignores, example:
This is a problem
- This is a problem
- This is a problem
- This is a problem
- And so on…
So I think that I need at code that will replace “-“ if it is the first character or it is after a return, but I can’t figure out how to do that. Then run my remove returns code from above. Can anyone help?
Feb 28 '11 #1
Share this Question
Share on Google+
7 Replies


P: 83
If the string to be removed is always " - "
space + dash + space
then use something like

Replace (Description," - "," ")

You would have to repeat this replace until no more
" - " strings exist.
This will not affect "1-800..." or "on-line" etc

**I'd be cautious depending on a memo field to be proper paragraph structure.

You may want to create a function to reformat a memo field whenever it is updated.
Feb 28 '11 #2

100+
P: 112
That will not work because there are sentences like “Object is – 23cm wide” and the code would replace that “-“because it has a space on either side. Also, most of the point form records are actually “-This is a problem” with no space, but some are “- This is a problem” with one space. This is why I need to have a code that eliminates only if it is the first character or after a return. Sorry if the first posts was confusing, the data in this memo field follows no specific format.
Feb 28 '11 #3

P: 83
Well, that is why I said if the string to be replaced is
always " - ".

The only other advice/approach I can think of is Regular expressions.
You may want to Google that for some explanation and samples.

This could be set up in Access using vba and setting a reference to the appropriate library.

Good luck
Feb 28 '11 #4

100+
P: 122
I threw together a quick VBA function that should accomplish what you want. This would need to be done before removing the returns (or you could make that part of the function, after the rest has been executed.)

Basically all you have to do is loop through, searching for the first hyphen after each return that is found. It then checks to make sure there are no non-space characters between them, so hyphens in phone numbers or compound words will be left alone.

Expand|Select|Wrap|Line Numbers
  1. Public Function DePoint(MemoText)
  2.   'Look for first hyphen in the source text, and remove if it is the first non-space character
  3.   x = InStr(MemoText, "-")
  4.   If Trim(Left(MemoText, x - 1)) = "" Then
  5.     MemoText = Replace(MemoText, "-", "", 1, 1)
  6.   End If
  7.  
  8.   'Check remaining lines
  9.   startPos = 1
  10.   Do
  11.     x = InStr(startPos, MemoText, Chr(13))
  12.     If x = 0 Then
  13.       'end of string reached
  14.       Exit Do
  15.     Else
  16.       'skip over carriage return & line feed characters
  17.       x = x + 2
  18.     End If
  19.  
  20.     y = InStr(x, MemoText, "-")
  21.     If Trim(Mid(MemoText, x, y - x)) = "" Then
  22.       MemoText = Left(MemoText, y - 1) & Right(MemoText, Len(MemoText) - y)
  23.     End If
  24.  
  25.     startPos = x
  26.   Loop
  27.   DePoint = MemoText
  28. End Function
Test input:
Expand|Select|Wrap|Line Numbers
  1. -This is a problem
  2. - This is a problem
  3.  -This is a problem
  4.  - Call me at 555-123-4567
  5. -In-line hyphens are left alone
Output:
Expand|Select|Wrap|Line Numbers
  1. This is a problem
  2.  This is a problem
  3.  This is a problem
  4.   Call me at 555-123-4567
  5. In-line hyphens are left alone
Mar 1 '11 #5

100+
P: 112
Hi gershwyn this code looks great but I am not sure how to run it in my update query? I put it into a module but don't know how to run a module off an update query? How do I enter it into design view so that it will run? I have never used modules before. Or am I doing this wrong?
Mar 1 '11 #6

100+
P: 112
If anyone can answer the previous post about how I would use the above code I would still like an answer to that. However, I have been able to figure out how to do this without using the code above, it is not as clean but still works. I run a series of update queries on my field to get the same results. See below:
Run these one at a time on your field in an update query.
Starts with:
- This is a problem
-This is a problem
-This is a problem
-519-765-3212
In-Line Hyphens

Removes “-“ if at start of field and Trims any spaces

Update to:
Expand|Select|Wrap|Line Numbers
  1. Trim(Replace([MyFeild],"-","",1,1))
Criteria:
Expand|Select|Wrap|Line Numbers
  1. Like “-*”
This is a problem
-This is a problem
- This is a problem.
-519-765-3212.
In-Line Hyphens

Removes case returns and line feeds and replaces them with “@” (@ is a character that I do not have in that field)

Update to:
Expand|Select|Wrap|Line Numbers
  1. Replace([MyFeild],Chr(13) & Chr(10),"@")
This is a problem@-This is a problem@- This is a problem.@-519-765-3212.@In-Line Hyphens

Removes “@-“ and replaces with “. “

Update to:
Expand|Select|Wrap|Line Numbers
  1. Replace([MyFeild],"@-",". ")
This is a problem. This is a problem. This is a problem.. 519-765-3212.@In-Line Hyphens


Removes “@“ and replaces with “ “

Update to:
Expand|Select|Wrap|Line Numbers
  1. Replace([MyFeild],"@",". ")
This is a problem. This is a problem. This is a problem.. 519-765-3212.. In-Line Hyphens

Removes spaces before a period if there is any
Update to:
Expand|Select|Wrap|Line Numbers
  1. Replace([MyFeild]," .",".")
This is a problem. This is a problem. This is a problem.. 519-765-3212.. In-Line Hyphens

Removes double period
Update to:
Expand|Select|Wrap|Line Numbers
  1. Replace([MyFeild],".. ",". ")
This is a problem. This is a problem. This is a problem. 519-765-3212. In-Line Hyphens


Removes double spaces, may have to be run multiple times!
Update to:
Expand|Select|Wrap|Line Numbers
  1. Replace([MyFeild],"  "," ")
This is a problem. This is a problem. This is a problem. 519-765-3212. In-Line Hyphens

It works but a lot of updateing. However it only has to be done once!
Thanks
Mar 2 '11 #7

100+
P: 122
Once you've put the code into your module, you would call it just like any other function. In your original post, you said you had an update query using the Replace function. Just change the entry in the Update To field (in query design view) to:
Expand|Select|Wrap|Line Numbers
  1. Depoint([Description])
Mar 2 '11 #8

Post your reply

Sign in to post your reply or Sign up for a free account.