1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

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

Discussion in 'Business Applications' started by jkgraham, Jan 27, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. jkgraham

    jkgraham Thread Starter

    Joined:
    Jan 27, 2011
    Messages:
    5
    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.
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    jkgraham, welcome to the Forum.
    What do you get if you actually run the Query?
     
  3. jkgraham

    jkgraham Thread Starter

    Joined:
    Jan 27, 2011
    Messages:
    5
    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.
     
  4. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    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?
     
  5. jkgraham

    jkgraham Thread Starter

    Joined:
    Jan 27, 2011
    Messages:
    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.
     
  6. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Did you try the second query method?
     
  7. jkgraham

    jkgraham Thread Starter

    Joined:
    Jan 27, 2011
    Messages:
    5
    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.
     
  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    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.
     
  9. jkgraham

    jkgraham Thread Starter

    Joined:
    Jan 27, 2011
    Messages:
    5
    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.
     
  10. Sponsor

As Seen On
As Seen On...

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.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/977311