June 24, 2013 at 10:29 am
Question/Problem: I am using Microsoft Access forms/queries to filter on a SQL table as in the following:
SELECT dbo_Premier.CustName, dbo_Premier.Address, dbo_Premier.City, dbo_Premier.State, dbo_Premier.[Zip Code]
FROM dbo_Premier
WHERE (((dbo_Premier.Address) Like [Forms]![frmRosterSearch]![FindAdd] & "*"));
This filter returns incorrect results. The results seem to have no logical pattern that I can see what my error is. It appears the correct records are returned, but with lots of incorrect results also.
When I do the exact same filter on a local table in Microsoft Access with the same information in it, the results are returned correctly.
At first I thought that somehow maybe the information wasn’t being cleared out of the SQL table correctly because weekly I clear and repopulate this table, however, when I tried to build a brand new table in SQL from the local table, the filter was still incorrect.
Using:
SQLServer 2008 Standard
Access 2007
June 24, 2013 at 1:22 pm
SQL server uses % as the wildcard.
See if the last few items in the following article clarifies the situation.
June 24, 2013 at 3:55 pm
Thank you for the post.
This wouldn't be applicable because I am querying via a .mdb or .accd file, not an access project.
Any other thoughts?
June 24, 2013 at 4:32 pm
I think the source of your data may be more important (SQL Server) than that where you are querying from. If I am understanding correctly the Access query is interpreted as it is passed to a linked table.
Some other thoughts.
Run profiler on SQL server to see what is really being passed to SQL server from your mdb.
Reduce the complexity of your query as a test. You concatenating form import with the wildcard. Try hard-coding everything in your query and avoiding the concatenation.
Curious to see what profiler says. When I say Like "A*" in an access query profiler says LIKE 'A%' )
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply