Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2006
    Posts
    65

    Unanswered: querying string values

    I have a database full of different postcodes, i want the records to be grouped by post code areas, i.e. ST1. To do this in Oracle you would use LIKE '%ST1%'

    how is this done in Access? please help

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by waylander
    I have a database full of different postcodes, i want the records to be grouped by post code areas, i.e. ST1. To do this in Oracle you would use LIKE '%ST1%'

    how is this done in Access? please help
    I guess you mean that in Oracle you would use this to limit the return to specific areas rather than group it by them?

    Also - '%ST1%' will also return ST11, ST12, ST13 yes?

    Anyhoo - short answer is replace % with * and check out Wildcards in Access help.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by pootle flump
    I guess you mean that in Oracle you would use this to limit the return to specific areas rather than group it by them?

    Also - '%ST1%' will also return ST11, ST12, ST13 yes?

    Anyhoo - short answer is replace % with * and check out Wildcards in Access help.

    HTH
    Poots - Remember that both work in Access, just in different areas ... "%" works for VBA SQL statements and "*" works in the QBE and stored queries ...

    As to the original question: You'd wildcard using LIKE "ST1*" or LIKE "ST1%" depending on where it's run from ... Remember (or learn for the 1st time) that a wildcard at the very beginning will pattern match ANYWHERE within the target string ...
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    OR.....
    you could of course just turn on ANSI SQL compatability mode and ignore the quirks of Access -JET/SQL in this area at least...
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by M Owen
    Poots - Remember that both work in Access, just in different areas ... "%" works for VBA SQL statements and "*" works in the QBE and stored queries ...
    Good point - though only in ADO. I believe the Lesser Arts (aka DAO) don't use the ANSI wildcards (actually - I more than believe so cause I just tested so I didn't look like a n00b )
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by pootle flump
    Good point - though only in ADO. I believe the Lesser Arts (aka DAO) don't use the ANSI wildcards (actually - I more than believe so cause I just tested so I didn't look like a n00b )
    I'll take your word for it ... I really don't do any DAO ... Although, I think that with DAO the asterisk is the wildcard ...
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    Apr 2006
    Posts
    65
    thank you very much its working fine now

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by pootle flump
    Good point - though only in ADO. I believe the Lesser Arts (aka DAO) don't use the ANSI wildcards (actually - I more than believe so cause I just tested so I didn't look like a n00b )
    Its a long shile since I used DAO in anger, probably it shows........
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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