Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts
    I have a query, qrySorting, that uses another query qryContribTotals (a Totals Query). When I view qrySorting I get an error msg that says: Data Mismatch in Criteria Expression. The problem is, there are no criteria. However, if I put the criteria >0 in the field SumofContrib (from the Totals query) the query executes correctly. What's going on?

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    One common situation is where you design a query and before you save it you apply a filter or a sort. Then you decide it worked pretty well and you save it. In those situations you may get a message like that. To clear it, look at the properties for the query and see if there is something in either the filter or the sort columns.
    Wendell

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts
    Checked the query properties and found that both Filter and Order By are blank. What should I try next?

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    [quote name='Howard Hillman' post='792264' date='05-Sep-2009 08:48']Checked the query properties and found that both Filter and Order By are blank. What should I try next?[/quote]
    We will need to see the query string to be able to do much else with it - you should be able to do the SQL view, and then copy that and paste it into a reply. Chances are there is some sort of garbage character in the WHERE clause of the SQL statement, but they can be a real bear to find.
    Wendell

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts
    Here is the SQL without and with the criteria. Of course there is no WHERE clause when there are no criteria:

    SELECT qryContribTotals.ContactID, qryContribTotals.LastName, qryContribTotals.FirstName, qryContribTotals.StateOrProvince, qryContribTotals.CountOfLastName, qryContribTotals.CompanyName, qryContribTotals.SumOfContribution, sort([SumofContribution]) AS SortNumber
    FROM qryContribTotals
    ORDER BY qryContribTotals.LastName, qryContribTotals.SumOfContribution DESC;

    SELECT qryContribTotals.ContactID, qryContribTotals.LastName, qryContribTotals.FirstName, qryContribTotals.StateOrProvince, qryContribTotals.CountOfLastName, qryContribTotals.CompanyName, qryContribTotals.SumOfContribution, sort([SumofContribution]) AS SortNumber
    FROM qryContribTotals
    WHERE (((qryContribTotals.SumOfContribution)>0))
    ORDER BY qryContribTotals.LastName, qryContribTotals.SumOfContribution DESC;

    The good news is that I played around with the second SQL, deleted the WHERE clause, saved the query, closed Access and tried again.
    And, it worked. So, creating the WHERE clause and deleting it seemed to fix the problem, even though there was no WHERE clause to begin with. Ain't software grand?

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I've had something like that happen where I was playing with a query and had a WHERE clause, and had done a filter, and had run it a couple of times, and then deleted the WHERE and saved the query, and kept getting a parameter prompt. Not sure what caused it, but glad you were able to resolve it. Thanks for sharing the "solution".
    Wendell

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts
    Always glad to share even when I stumble upon the "solution". Thanks for your suggestions. I learned a little more about SQL.

Posting Permissions

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