Special characters

Discussion in 'Access General' started by Ixtreme, Jul 30, 2013.

  1. Ixtreme

    Ixtreme Guest

    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?
     
    Ixtreme, Jul 30, 2013
    #1
    1. Advertisements

  2. 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
     
    John W. Vinson, Jul 30, 2013
    #2
    1. Advertisements

  3. Ixtreme

    Ixtreme Guest

    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?
     
    Ixtreme, Jul 31, 2013
    #3
  4. Ixtreme

    Guest

    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
     
    , Jul 31, 2013
    #4
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.