Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Location
    Hong Kong
    Posts
    29

    Unanswered: Retrieve Data when value of a field starts with X

    I would like to retrieve certain Records, in which the value starts with X
    e.g. Xa,Xb,Xc. For normal SQL, it should be like
    Select * from Table1 where field1 like 'X%', I donno how to write the statement using ACCESS, or achieve this function

    Many Thanx in advance

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Sure looks like you did it to me ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    May 2005
    Posts
    1,191
    The only thing I see you have to change is the % sign, the asterisk (*) is the wildcard character.

    like 'X*'

    For more info, do an Access help search on Wildcard Characters, you get the following:
    The asterisk (*), question mark (?), number sign (#), exclamation point (!), hyphen (-), and brackets ([ ]) are wildcard characters. You can use these characters in queries and expressions to include all records, file names, or other items that begin with specific characters or match a certain pattern. You can also use wildcard characters and matching characters to further refine a search when using an SQL statement.

    Symbol Example Usage

    *
    wh* finds what, white, and why
    *at finds cat, bat, and what
    Matches any number of characters, and can be used anywhere in the character string.

    ?
    b?ll finds ball, bell, and bill
    Matches any single character.

    #
    1#3 finds 103, 113, 123
    Matches any single digit.

    [ ]
    b[ae]ll finds ball and bell but not bill
    Matches any single character within the brackets.

    !
    b[!ae]ll finds bill and bull but not bell or ball
    Matches any character not in the list.

    -
    b[a-c]d finds bad, bbd, and bcd
    Matches any one of a range of characters.


    Note The wildcard characters * (asterisk), ? (question mark), # (number sign), and [ (opening bracket) can match themselves only if enclosed in brackets.
    Me.Geek = True

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Nick Nick Nick ... Both the "*" and the "%" are wildcards ... The "*" is when using a query in the QBE (or saved as a stored query). The "%" is when the query is run under VBA code.
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    May 2005
    Posts
    1,191
    Hmm... Learn something new everyday. Thanks Mike
    Me.Geek = True

  6. #6
    Join Date
    Mar 2004
    Location
    Hong Kong
    Posts
    29
    Thanx both of you for the quick replies! Your explanations are very detailed too.

  7. #7
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    More specifically

    * is the wild card for Access query designer AND DAO recordsets and

    % is the wild card for ADO recordsets

    ??

    MTB


    MTB

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •