Results 1 to 6 of 6
  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Doing a filter on a SQL linked table (Access 2003 SP3)

    I thought it was just the dates that was incorrectly filtering, but it is also a problem on text fields.

    If i filter (right clicking the field in the table) and typeing #7/8/08# ig get those dates with other dates as well.

    If i filter on a text field Like "LIPITOR*" if get mostly LIPITOR but also get non related records as well.

    Can anyone help me here?

  2. #2
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Doing a filter on a SQL linked table (Access 2003 SP3)

    Hi Pat,
    It has been some time since I've used Acc03 but "I think" what you are doing requires the same delimiters/symbols as does SQL Server. Dates are delimited by single quotes......'7/8/08' (without a time part SQL will assume 12:00 midnight) as dates are string values in SQL Server and the Wild Card character is a % not the *........"LIPITOR%". I know SQL requires these when using ADO or an ADP, but as I said it has been some time (well a long time really) since using 2003. Acc07 changed the right-click filter on a table field significantly concerning dates. Here are a couple of links that may help you a bit. TechRepublic and Utter Access
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  3. #3
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Doing a filter on a SQL linked table (Access 2003 SP3)

    Hi Pat,
    On further thought, right clicking on a field I am dimly remembering it does not require delimiters; i.e. quotation marks, #, *, etc. I think the odbc connector should handle the correct method to present the filter to SQL Server. The rest of the previous reply does hold though for ADO and ADP's.
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Doing a filter on a SQL linked table (Access 2003 SP3)

    I understand the ' and % hars.

    I am composing the query to limit the dates by delimiting the date i enter (eg #1/10/208#) as access likes it that way. If i try to delimit the date with ' i get an access error.

    My problem is that i get the majority of records right with a sprinkling of records that are not equal to the date i am filtering on. The same problem exists where i filter for LIPITOR, the majority of records returned are in fact LIPITOR wth a sprinkling of other records as well.

    Te query does not return all records in the table.

    Those articles you offered are invaluable, thank you.

  5. #5
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Doing a filter on a SQL linked table (Access 2003 SP3)

    Hi Pat,
    Sorry for the misunderstanding.
    I've just now been able to test this on a virtual computer using SQL Server 2005 Express (from previous posts I am assuming this is what you are using) and Acc2003 and I am not getting the same results as you are. (right clicking to filter on the field). (btw, it seems to not matter, when right clicking on a field, entering your filter criteria into the textbox for "Filter for:" and using or not using # for dates or using, or not, double quotes around a string).
    Since you've mentioned that you are composing a query I checked that out as well. There also I do not receive the mixed results as you are seeing. So I guess I don't understand what is happening. What are the "sprinkling of other records as well" when filtering with LIPITOR? Are they similar or way out, such as returning ASPIRIN?
    Thanks.
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Doing a filter on a SQL linked table (Access 2003 SP3)

    Hi Pat,
    We also do this routinely with SQL 2000, 2005 and 2008 and don't get those sort of results. One issue can be date/time fields where you have an entry for time in addition to the date - but that usually causes you to not get records you should. And on text fields we really don't see problems at all. Are your tables connected using ODBC System data sources? And what version of driver are you using? You might get some odd results trying to use the 2000 version of the driver with 2005 or 2008. My recommendation would be to use the SQL Server Native Client version 10 - released by Microsoft a couple of months ago - it solved several connectivity problems for us.
    Wendell

Posting Permissions

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