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

Topic

New Reply

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?

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote