Eliminating certain punctuation

[Bruce Rodtnick]

Is there a way for a proceedure to ignore a punctuation mark, such as a
question mark (?).

What I'm trying to do is have my program find a document on my hard
drive, but Long File Names will not recognize a question mark but my
database item has a question mark in the title; i.e. Where Are You
Going? will look for "Where Are You Going.doc.

 

[Nikos Yannacopoulos]

Bruce,

The following two lines of code will assign value "Where are you going.doc"
to str2.

str1 = "Where are you going?.doc?"
str2= replace( str1, "?","")

If you are doing the whole thing with code just insert them as appropriate.
If you want to do this outside of a module, insert the following function in
any general module:

Function Clean_punct(str1)
Clean_punkt= replace( str1, "?","")
End Function

Then you can call this function from anywhere in your database (queries,
forms, reports, macros etc.) like:
=Clean_punct ("string to be cleaned up")

Note: you can use several replace statements in sequence to get rid of more
unwanted cahracters, e.g.:

Function Clean_punct(str1)
str1 = replace( str1, "&","")
str1 = replace( str1, "%","")
str1 = replace( str1, "#","")
str1 =replace( str1, "?","")
Clean_punkt=str1
End Function

Or you could (a) maintain unwanted characters in a table, open it as a
recordset and loop through removing one at a time, or (b) specify unwanted
ASCII ranges and loop through removing one at a time.

HTH,
Nikos

 

[Bruce Rodtnick]

Your code does the trick. It works fine except as a global Function. I don't
know if I'm doing it right. The code I'm using is this:

FileNameAndPath = (Me!Name & ".mp3")
FileNameAndPath = Replace(FileNameAndPath, "?", "")
Application.FollowHyperlink FileNameAndPath, , False, False

and it works.

I put in a function like you said and called it with:

FileNameAndPath = (Me!Name & ".mp3")
Clean_Punct (FileNameAndPath)
Application.FollowHyperlink FileNameAndPath, , False, False

My Module is:

Function Clean_Punct(FileNameAndPath)
FileNameAndPath = Replace(FileNameAndPath, "?", "")
End Function

Where am I wrong?

B

 

[Nikos Yannacopoulos]

Bruce,

In theory this should work, provided you have declared FileNameAndPath as a
global variable in a general module; otherwise the "?" removal when the
fiunction is run is kept locally within the function, whereas in your main
code the "?" is still there when you run line

Application.FollowHyperlink FileNameAndPath, , False, False

You missed a key functionality of Function, though; as opposed to a sub, a
function returns a value just like any built-in function; this eliminates
the need for a global variable. Just change your code to:

FileNameAndPath = (Me!Name & ".mp3")
Application.FollowHyperlink Clean_Punct (FileNameAndPath), , False, False

and

Function Clean_Punct(FileNameAndPath)
Clean_Punct = Replace(FileNameAndPath, "?", "")
End Function

Regards,
Nikos