Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Access 2007 "Sort:" and "Criteria:" not working in Datasheet View


(!)

jkgraham's Avatar
jkgraham jkgraham is offline
Junior Member with 5 posts.
THREAD STARTER
 
Join Date: Jan 2011
Experience: Advanced
27-Jan-2011, 11:45 AM #1
Access 2007 "Sort:" and "Criteria:" not working in Datasheet View
When I am building a query in Access 2007, the "Sort:" and "Criteria:" selctions don't work when I switch from 'Design View' to 'Datasheet View'.
If I change the query type from 'Select' to 'Make Table' and run the query, the data in the new table mathces the "Sort:" and "Criteria:" selctions correctly.
What is wrong with my Select Query - Datasheet View? This is very annoying.
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,608 posts.
 
Join Date: Mar 2005
Location: UK
27-Jan-2011, 11:52 AM #2
jkgraham, welcome to the Forum.
What do you get if you actually run the Query?
jkgraham's Avatar
jkgraham jkgraham is offline
Junior Member with 5 posts.
THREAD STARTER
 
Join Date: Jan 2011
Experience: Advanced
27-Jan-2011, 12:03 PM #3
FYI. The sorce data table is linked to SQL Server via ODBC. If the entire source table is copyed into a local table then the Select Query - Datasheet View works correctly. So it seems the problem only occurs when querying the linked table.
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,608 posts.
 
Join Date: Mar 2005
Location: UK
27-Jan-2011, 12:11 PM #4
Linking to SQL tables does have it's problems, what do you ultimately want to do with the data?
What happens if you remove the Sort and Criteria from the current Query and then produce a second query based on the first query (which should hold the data in memory if there is enough space) and then add the Sort & Criteria to the second query?
__________________
OBP
I do not give up easily
jkgraham's Avatar
jkgraham jkgraham is offline
Junior Member with 5 posts.
THREAD STARTER
 
Join Date: Jan 2011
Experience: Advanced
27-Jan-2011, 12:33 PM #5
I have used Access so much in the past (not Access 2007) that I always go to it first to view data. Since I have verified that my queries work OK with Local tables I’m not as worried as I was. Something must be wrong with the interface between Access and SQL-Server. Maybe it's my ODBC driver. I was hoping it was some strange option in Access 2007 that I didn't know about.

I inherited a SQL Server Database and Reporting System. I had to make a new report in Microsoft Visual Studio (which seems to be a great product). The new report counts records and sums up time in seconds and I wanted to verify that it is counting and summing correctly by copying it into Excel and doing it by hand to see if I get the same results as the report.
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,608 posts.
 
Join Date: Mar 2005
Location: UK
27-Jan-2011, 12:40 PM #6
Did you try the second query method?
jkgraham's Avatar
jkgraham jkgraham is offline
Junior Member with 5 posts.
THREAD STARTER
 
Join Date: Jan 2011
Experience: Advanced
27-Jan-2011, 12:57 PM #7
I tried a second query (with criteria) using the first query (without criteria) as the source table. The results were the same. I then added more criteria to more columns with no success.
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,608 posts.
 
Join Date: Mar 2005
Location: UK
27-Jan-2011, 01:04 PM #8
That is very odd, I would have expected that to have worked.
By the way I did some work with another poster on here using SQL and to get a VBA query to work properly we had to include the connection like this
Dim rsCustomer As ADODB.Recordset
Set rsCustomer = New ADODB.Recordset
rsCustomer.Open "SELECT BI FROM FichaCandidatura WHERE BI ='" & Me.BI & "'", CurrentProject.Connection, adOpenStatic, adLockOptimistic
txtCustomerNo = rsCustomer.Fields("BI")
rsCustomer.Close
#

So maybe your SQL statement in the query should also include it.
jkgraham's Avatar
jkgraham jkgraham is offline
Junior Member with 5 posts.
THREAD STARTER
 
Join Date: Jan 2011
Experience: Advanced
27-Jan-2011, 03:58 PM #9
I could not add the recordset to the query and make it work.
I changed the query to a 'Pass-Through' query and it worked.
As I mention earlier, if I change the query to a 'Make Table' it works as well. (So something's different there).
I'm OK with what I have but it is a bug that I would be interested in finding the source.

I wonder if I can log the SQL Statement that Access is sending over to SQL-Server somewhere to see how it is being formatted by Access or the ODBC-Driver. I looked in the ODBC driver configuration and it did have some logging functions but it didn't log the actual SQL Statement that was sent. Maybe I can find a logging feature in SQL-Server.
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
WELCOME TO TECH SUPPORT GUY!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


Similar Threads
Title Thread Starter Forum Replies Last Post
AND operator not working in MS Access Expression Builder Misha2010 Business Applications 9 13-Sep-2010 10:53 AM
Solved: Outlook 2007 links not working in Windows 7 RodneyTindall Web & Email 4 04-Feb-2010 06:11 PM
Access 2007 query and form calculation with checkboxes Margo12 Business Applications 4 16-Aug-2009 09:38 AM
Solved: Microphone doesnt work in CS and CS:S Please help thrash_til_death Games 1 09-Nov-2008 10:53 PM
Solved: Microphone doesnt work in CS and CS:S Please help thrash_til_death Hardware 1 09-Nov-2008 10:51 PM

WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑