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

[Redbeard]

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:

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:

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?

[orangeCat]

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.

[Redbeard]

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.

[orangeCat]

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

[gershwyn]

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.

Public Function DePoint(MemoText)
  'Look for first hyphen in the source text, and remove if it is the first non-space character
  x = InStr(MemoText, "-")
  If Trim(Left(MemoText, x - 1)) = "" Then
    MemoText = Replace(MemoText, "-", "", 1, 1)
  End If
 
  'Check remaining lines
  startPos = 1
  Do
    x = InStr(startPos, MemoText, Chr(13))
    If x = 0 Then
      'end of string reached
      Exit Do
    Else
      'skip over carriage return & line feed characters
      x = x + 2
    End If
 
    y = InStr(x, MemoText, "-")
    If Trim(Mid(MemoText, x, y - x)) = "" Then
      MemoText = Left(MemoText, y - 1) & Right(MemoText, Len(MemoText) - y)
    End If
 
    startPos = x
  Loop
  DePoint = MemoText
End Function

Test input:

-This is a problem
- This is a problem
 -This is a problem
 - Call me at 555-123-4567
-In-line hyphens are left alone

Output:

This is a problem
 This is a problem
 This is a problem
  Call me at 555-123-4567
In-line hyphens are left alone

[Redbeard]

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?

[Redbeard]

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:

Trim(Replace([MyFeild],"-","",1,1))

Criteria:

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:

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:

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:

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:

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:

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:

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

[gershwyn]

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:

Depoint([Description])