validation question

  • Thread starter Thread starter M. Murphy
  • Start date Start date
M

M. Murphy

Hi,
Database saves a report as a pdf, that part works great.

One field is "Company name" and I use this to create the file name of the
pdf.
Someone put a backslash in one of the company names at one time, and access
would not save this one report because of the backslash. It took me a few
hours to figure out why this was happening.
How can I validate this one field so that there is not a backslash anywhere
in this field? I have tried to do this, but my attempts only work if the
backslash is the only thing in the field. for example, if compnay name is \
the validation works, but if the name is xxx\zzz the validation does not
catch it.
Is this even possible?

TIA
 
Look at the instr function in Help. Searches a string for a character. or
you could try this function I use for a whole bunch of naughty characters!--
call the function with your string name :

Me.CompanyName = StripString(CompanyName)

' Returns a string minus a set of specified chars.
Function StripString(Mystr As String)
On Error GoTo StripStringError
Dim strChar As String, strHoldString As String
Dim i As Integer
' Exit if the passed value is null.
If IsNull(Mystr) Then Exit Function

' Check each value for invalid characters.
For i = 1 To Len(Mystr)
strChar = Mid$(Mystr, i, 1)
Select Case strChar

Case ".", "$", ":", "!", "@", "#", "%", "^", "&", "*", "(",
")", ",", ">", "'", """", ",", " ", "<", "/", "\", ";", "?"
' Do nothing if bad character is found

Case Else 'add the good character to your string
strHoldString = strHoldString & strChar
End Select
Next i
' Pass back corrected string as upper case( remove UCase if you
don't want it).
StripString = UCase(strHoldString)

StripStringEnd:
Exit Function
StripStringError:
MsgBox Error$
Resume StripStringEnd
End Function

HTH
Damon
 
Looks good, I will try it.

Thank you.


Damon Heron said:
Look at the instr function in Help. Searches a string for a character. or
you could try this function I use for a whole bunch of naughty
characters!-- call the function with your string name :

Me.CompanyName = StripString(CompanyName)

' Returns a string minus a set of specified chars.
Function StripString(Mystr As String)
On Error GoTo StripStringError
Dim strChar As String, strHoldString As String
Dim i As Integer
' Exit if the passed value is null.
If IsNull(Mystr) Then Exit Function

' Check each value for invalid characters.
For i = 1 To Len(Mystr)
strChar = Mid$(Mystr, i, 1)
Select Case strChar

Case ".", "$", ":", "!", "@", "#", "%", "^", "&", "*", "(",
")", ",", ">", "'", """", ",", " ", "<", "/", "\", ";", "?"
' Do nothing if bad character is found

Case Else 'add the good character to your string
strHoldString = strHoldString & strChar
End Select
Next i
' Pass back corrected string as upper case( remove UCase if you
don't want it).
StripString = UCase(strHoldString)

StripStringEnd:
Exit Function
StripStringError:
MsgBox Error$
Resume StripStringEnd
End Function

HTH
Damon
Click to expand...
 
ok, one question.

excuse my ignorance, but where do I call this function from? I just tried
putting it in a test form as an event in before update, and access didnt
like that too much.

I put the function in a module first.
 
strike that last question, I got that part, but that leads to another
question,
(nice code by the way...)
I want to strip out the backslash and replace it with a space, I am still
trying, maybe I can figure it out before you answer...

Thank you!!!
 
I want to strip out the backslash and replace it with a space, I am still
trying, maybe I can figure it out before you answer...
Click to expand...

If you JUST want to replace all backslashes with spaces, update the field to

Replace([fieldname], "\", " ")

Of course this won't catch all the other special characters.
 
Back
Top