Invalid Characters.

  • Thread starter Thread starter Jessie
  • Start date Start date
J

Jessie

Hello.

I have kind of a two part question, but in the same sort
of category:

1. Is there a way to programmatically search all fields
for invalid Caracters and replace with spaces.
For example I have a table which we recieve with
dashes, dots, commas and other characters we send that
table back in text format and are not allowed to transmit
those characters. Is there a way to replace any instance
of those charcaters in the entire table?

2. Is there a way to replace all Null fields in a table
with 0?

Thanks for any help you can offer me.

Jessie
 
Hello.

I have kind of a two part question, but in the same sort
of category:

1. Is there a way to programmatically search all fields
for invalid Caracters and replace with spaces.
For example I have a table which we recieve with
dashes, dots, commas and other characters we send that
table back in text format and are not allowed to transmit
those characters. Is there a way to replace any instance
of those charcaters in the entire table?
Click to expand...

Yes, with a little difficulty. You could run an Update query with a
criterion of

LIKE "*[,.-]*"

(using your list of invalid characters between the brackets); you
could then Update to

REPLACE(REPLACE(REPLACE([fieldname], ",", " "),".", " "),"-", " ")

Or... if the list of invalid characters is lengthy - you may want to
write a little VBA script to parse the text. Air code to replace
anything that's not alphanumeric:

Private Sub ZapSpecial(strIn As String) As String
Dim iPos As Integer
Dim strC As String
ZapSpecial = ""
For iPos = 1 to Len(strIn)
strC = Mid(strIn, iPos, 1)
If InStr(UCase(strC),"ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789") = 0 Then
ZapSpecial = ZapSpecial & " "
Else
ZapSpecial = ZapSpecial & strC
End If
End Sub

Use your own string of allowed characters.
2. Is there a way to replace all Null fields in a table
with 0?
Click to expand...

Much easier. Run an UPDATE query updating every numeric field to

NZ([fieldname])
 
Back
Top