Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Excel 2003 not understanding Access 2003 "like"

Discussion in 'Information Technology' started by Martin, Nov 5, 2005.

  1. Martin

    Martin
    Expand Collapse
    Guest

    I have a number of Access queries linked and displayed in an Excel workbook
    by way of Data, Import External Data, Import Data. Some of the criteria and
    select statements in the queries use the keyword "Like" (e.g. Like "*Smith"
    to pull up all names ending in "Smith"). These statements seem to be
    completely ignored by Excel so that records I should see are not appearing in
    the workbook. This worked fine in versions 97 (from which we've just
    upgraded) so is this a version 2003 bug? If so, is there a fix? (Apologies
    for posting this twice but I wasn't sure if it's an Excel or an Access
    problem.)
     
  2. Loading...

    Similar Threads Forum Date
    Canada Sukhdeep Kaur Chohan of Brampton Wins PM Award for Teaching Excellence Breaking News Mar 5, 2014
    India Sikh Regiment's Passion for Excellence Breaking News May 19, 2013
    Leisure Excellence -skills-Attitude Business, Lifestyle & Leisure Feb 14, 2010
    Katha Excellent Katha by Bhai Ram Singh Kathavachak Videos Aug 8, 2009
    [SPN] Celebrating Five Years of Excellence & Learning Announcements Jun 3, 2009

  3. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    How are you connecting to Excel: through DAO or ADO?

    If you're using ADO, the wildcard character is %, not *.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Martin" <Martin@discussions.microsoft.com> wrote in message
    news:7CAF0440-F015-45C3-999C-A52D74617C93@microsoft.com...
    > I have a number of Access queries linked and displayed in an Excel

    workbook
    > by way of Data, Import External Data, Import Data. Some of the criteria

    and
    > select statements in the queries use the keyword "Like" (e.g. Like

    "*Smith"
    > to pull up all names ending in "Smith"). These statements seem to be
    > completely ignored by Excel so that records I should see are not appearing

    in
    > the workbook. This worked fine in versions 97 (from which we've just
    > upgraded) so is this a version 2003 bug? If so, is there a fix?

    (Apologies
    > for posting this twice but I wasn't sure if it's an Excel or an Access
    > problem.)
     
  4. Martin

    Martin
    Expand Collapse
    Guest

    Thanks but I'm not using code - just connecting to an Access query from Excel
    using the import External Data command from the Data menu.



    "Douglas J Steele" wrote:

    > How are you connecting to Excel: through DAO or ADO?
    >
    > If you're using ADO, the wildcard character is %, not *.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "Martin" <Martin@discussions.microsoft.com> wrote in message
    > news:7CAF0440-F015-45C3-999C-A52D74617C93@microsoft.com...
    > > I have a number of Access queries linked and displayed in an Excel

    > workbook
    > > by way of Data, Import External Data, Import Data. Some of the criteria

    > and
    > > select statements in the queries use the keyword "Like" (e.g. Like

    > "*Smith"
    > > to pull up all names ending in "Smith"). These statements seem to be
    > > completely ignored by Excel so that records I should see are not appearing

    > in
    > > the workbook. This worked fine in versions 97 (from which we've just
    > > upgraded) so is this a version 2003 bug? If so, is there a fix?

    > (Apologies
    > > for posting this twice but I wasn't sure if it's an Excel or an Access
    > > problem.)

    >
    >
    >
     
  5. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Did you try using %, rather than *, just in case that's the problem?

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Martin" <Martin@discussions.microsoft.com> wrote in message
    news:185CBF44-5C5A-480A-9B90-87F8BE304679@microsoft.com...
    > Thanks but I'm not using code - just connecting to an Access query from

    Excel
    > using the import External Data command from the Data menu.
    >
    >
    >
    > "Douglas J Steele" wrote:
    >
    > > How are you connecting to Excel: through DAO or ADO?
    > >
    > > If you're using ADO, the wildcard character is %, not *.
    > >
    > > --
    > > Doug Steele, Microsoft Access MVP
    > > http://I.Am/DougSteele
    > > (no e-mails, please!)
    > >
    > >
    > > "Martin" <Martin@discussions.microsoft.com> wrote in message
    > > news:7CAF0440-F015-45C3-999C-A52D74617C93@microsoft.com...
    > > > I have a number of Access queries linked and displayed in an Excel

    > > workbook
    > > > by way of Data, Import External Data, Import Data. Some of the

    criteria
    > > and
    > > > select statements in the queries use the keyword "Like" (e.g. Like

    > > "*Smith"
    > > > to pull up all names ending in "Smith"). These statements seem to be
    > > > completely ignored by Excel so that records I should see are not

    appearing
    > > in
    > > > the workbook. This worked fine in versions 97 (from which we've just
    > > > upgraded) so is this a version 2003 bug? If so, is there a fix?

    > > (Apologies
    > > > for posting this twice but I wasn't sure if it's an Excel or an Access
    > > > problem.)

    > >
    > >
    > >
     
  6. Martin

    Martin
    Expand Collapse
    Guest

    Thanks Doug - that did the job. Didn't realise DAO (and now ADO) were used
    for these links. Are there any utilities out there to help with this? My
    queries are relatively simple but problems of course arise when the query
    being linked to Excel is based upon other queries within Access - you can't
    just replace * with % in earlier queries in the chain because Access itself
    can't interpret the % sign.


    "Douglas J Steele" wrote:

    > Did you try using %, rather than *, just in case that's the problem?
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "Martin" <Martin@discussions.microsoft.com> wrote in message
    > news:185CBF44-5C5A-480A-9B90-87F8BE304679@microsoft.com...
    > > Thanks but I'm not using code - just connecting to an Access query from

    > Excel
    > > using the import External Data command from the Data menu.
    > >
    > >
    > >
    > > "Douglas J Steele" wrote:
    > >
    > > > How are you connecting to Excel: through DAO or ADO?
    > > >
    > > > If you're using ADO, the wildcard character is %, not *.
    > > >
    > > > --
    > > > Doug Steele, Microsoft Access MVP
    > > > http://I.Am/DougSteele
    > > > (no e-mails, please!)
    > > >
    > > >
    > > > "Martin" <Martin@discussions.microsoft.com> wrote in message
    > > > news:7CAF0440-F015-45C3-999C-A52D74617C93@microsoft.com...
    > > > > I have a number of Access queries linked and displayed in an Excel
    > > > workbook
    > > > > by way of Data, Import External Data, Import Data. Some of the

    > criteria
    > > > and
    > > > > select statements in the queries use the keyword "Like" (e.g. Like
    > > > "*Smith"
    > > > > to pull up all names ending in "Smith"). These statements seem to be
    > > > > completely ignored by Excel so that records I should see are not

    > appearing
    > > > in
    > > > > the workbook. This worked fine in versions 97 (from which we've just
    > > > > upgraded) so is this a version 2003 bug? If so, is there a fix?
    > > > (Apologies
    > > > > for posting this twice but I wasn't sure if it's an Excel or an Access
    > > > > problem.)
    > > >
    > > >
    > > >

    >
    >
    >
     
  7. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Unfortunately, I'm not aware of any

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Martin" <Martin@discussions.microsoft.com> wrote in message
    news:DEEEF7E7-96AD-45A1-86DC-5FE752970BC5@microsoft.com...
    > Thanks Doug - that did the job. Didn't realise DAO (and now ADO) were

    used
    > for these links. Are there any utilities out there to help with this? My
    > queries are relatively simple but problems of course arise when the query
    > being linked to Excel is based upon other queries within Access - you

    can't
    > just replace * with % in earlier queries in the chain because Access

    itself
    > can't interpret the % sign.
    >
    >
    > "Douglas J Steele" wrote:
    >
    > > Did you try using %, rather than *, just in case that's the problem?
    > >
    > > --
    > > Doug Steele, Microsoft Access MVP
    > > http://I.Am/DougSteele
    > > (no e-mails, please!)
    > >
    > >
    > > "Martin" <Martin@discussions.microsoft.com> wrote in message
    > > news:185CBF44-5C5A-480A-9B90-87F8BE304679@microsoft.com...
    > > > Thanks but I'm not using code - just connecting to an Access query

    from
    > > Excel
    > > > using the import External Data command from the Data menu.
    > > >
    > > >
    > > >
    > > > "Douglas J Steele" wrote:
    > > >
    > > > > How are you connecting to Excel: through DAO or ADO?
    > > > >
    > > > > If you're using ADO, the wildcard character is %, not *.
    > > > >
    > > > > --
    > > > > Doug Steele, Microsoft Access MVP
    > > > > http://I.Am/DougSteele
    > > > > (no e-mails, please!)
    > > > >
    > > > >
    > > > > "Martin" <Martin@discussions.microsoft.com> wrote in message
    > > > > news:7CAF0440-F015-45C3-999C-A52D74617C93@microsoft.com...
    > > > > > I have a number of Access queries linked and displayed in an Excel
    > > > > workbook
    > > > > > by way of Data, Import External Data, Import Data. Some of the

    > > criteria
    > > > > and
    > > > > > select statements in the queries use the keyword "Like" (e.g. Like
    > > > > "*Smith"
    > > > > > to pull up all names ending in "Smith"). These statements seem to

    be
    > > > > > completely ignored by Excel so that records I should see are not

    > > appearing
    > > > > in
    > > > > > the workbook. This worked fine in versions 97 (from which we've

    just
    > > > > > upgraded) so is this a version 2003 bug? If so, is there a fix?
    > > > > (Apologies
    > > > > > for posting this twice but I wasn't sure if it's an Excel or an

    Access
    > > > > > problem.)
    > > > >
    > > > >
    > > > >

    > >
    > >
    > >
     

Share This Page