Special characters

[Ixtreme]

In VBA I need to create a query that check if a specific field contains special characters. Since the special characters could change, I want them in a separate table. So my database has a customer table that contains a field Street.

in the special character table 'TEST' I have 4 rows with values:

@
#
$
%

There are 5 records in the Customer table CUST
In one of these records I have a Streetline that contains 'Street@Line'
The query should retreive that specific record ID.
Is this something that could be done doing a dlookup somehow?

 

[John W. Vinson]

In VBA I need to create a query that check if a specific field contains special characters. Since the special characters could change, I want them in a separate table. So my database has a customer table that contains a field Street.

in the special character table 'TEST' I have 4 rows with values:

@
#
$
%

There are 5 records in the Customer table CUST
In one of these records I have a Streetline that contains 'Street@Line'
The query should retreive that specific record ID.
Is this something that could be done doing a dlookup somehow?

A couple of ways to do this. Probably the simplest would be a non-equi join:

SELECT CUST.* FROM CUST
INNER JOIN [TEST]
ON CUST.[Street] LIKE '*" & [TEST].[fieldname] & "*"

A DLookUp would be possible too but harder since there are multiple values to
test.

Alternatively, if the four special characters in TEST are pretty much static,
you could avoid it altogether with a wildcard query:

SELECT CUST.* FROM CUST
WHERE STREET LIKE "*[@#$%]*";

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Ixtreme]

Hi John,

Thanks, I'll go for the second option. However, if I want to do this is vba, this will give me an error. I need to check for the following characters:

'"!*#&^%$":;\|<>?@{}[]

strSQL = "SELECT CUST.* FROM CUST WHERE STREET LIKE ?? "

what should I place where the 2 ?? are?

 

[bdp222]

You want to check for quotes? How about taking their ascii values and test them like that..
dim txt as string
z, x as integer

txt = "test for & inclusion of # crazy characters"
for z = 1 to len(txt)
x = mid(txt, z, 1)
if ((asc(x) => 33) and (asc(x) <= 42) or _
((asc(x) => 92) and (asc(x) <= 96)
((asc(x) > 123) and (asc(x) < 126) then
msgbox x & ", character number " & z & " is a special character!"
end if
next z