Go Back   Access World Forums > Microsoft Access Reference > Code Repository

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-12-2015, 08:07 AM   #1
Rx_
Nothing In Moderation
 
Rx_'s Avatar
 
Join Date: Oct 2009
Location: Denver, Colorado
Posts: 2,794
Thanks: 635
Thanked 336 Times in 307 Posts
Rx_ has a spectacular aura about Rx_ has a spectacular aura about Rx_ has a spectacular aura about
Post Function Contains Illegal Characters In String

The InStr function can find a single character. In the case of a string in a table, illegal characters can cause problems with a query. Or perhaps they just should not be allowed for business rule reasons.
My SQL Server database uses Remote Servers connected to Oracle and other sources. In a import routine, my preference is to test (true/false) to determine if the strings meet nameing standards before importing them.

Keywords: This also uses an Array and a Loop

This is a short code summary of the function to find a list of illegal characters in a string.

Code:
Public Function ContainsIllegalCharactersInString(MyText As String) As Boolean
Dim Illegal As Variant, i As Integer, j As Integer
On Error GoTo err_TRAP
Illegal = Array("\", "/", ":", "*", "?", "#", """", _
 "<", ">", "|")
 For i = 0 To UBound(Illegal)
    j = InStr(1, MyText, Illegal(i))
    If j > 0 Then
        'MsgBox "illegal character! " & illegal(i) ' for debug or testing
        ContainsIllegalCharactersInString = True
        Exit Function
        Exit For
    End If
 Next
 ContainsIllegalCharactersInString = False
Exit Function
err_TRAP:
 Debug.Print "Function ContainsIllegalCharactersInString " & Err.Description
End Function
To test in a debug window enter the command with the ?:
? ContainsIllegalCharactersInString("My Good String")
False
? ContainsIllegalCharactersInString("My String Gone Bad?")
True

Uncomment the Message Box to see specific character

__________________
Were you lucky enough to get an answer? Please mark your question as [SOLVED] The original poster can go to Thread Tools to mark it as Solved.

Quotation Thomas Jefferson: "Peace is that brief glorious moment in history when everybody stands around reloading."

There are 2 Kinds of Countries on this Planet
1. Those that use the Metric System
2. Those that had a man walk on the moon

Denver, Colorado - The "Mile High City" - non-metric!
Rx_ is offline   Reply With Quote
The Following User Says Thank You to Rx_ For This Useful Post:
hassanogaibi (06-28-2016)
Reply

Tags
character , function , illegal , instr function , string

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Function to remove illegal xml characters haydenal Modules & VBA 12 09-04-2015 11:29 AM
[SOLVED] Illegal function call on Exit Function mdjks Modules & VBA 2 06-30-2015 08:59 AM
Checking a string for Characters txgeekgirl Forms 3 09-13-2010 10:17 AM
Function Format Excel Tab Name - remove characters from any string Rx_ Code Repository 0 08-25-2010 01:51 PM
7952 You made an illegal function call. chris01252 Modules & VBA 1 03-15-2007 04:35 AM




All times are GMT -8. The time now is 06:26 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World