Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2008
    Location
    Suburb of Chicago, Illinois
    Posts
    56

    Unanswered: Access 2003 - Data Mismatch error in query


    · I have an Access 2003 query that pulls about 20 fields from several ODBC data sources, one of which is dbo_OptimizeIt_ExcessUsages.
    · I use Group By to group the fields and for the field “Quantity” I use SUM.
    · I also need to select only those records where the summed quantity is greater than or equal to 7,000.
    · The quantity sums fine but when I try to enter the criteria to select only those records where the summed quantity is greater than or equal to 7,000 (>=7000) I get a “Data Mismatch” error.
    · In addition to using >=7000 I also tried using
    CSng((Sum([dbo_OptimizeIt_ExcessUsages].[Quantity]))>=7600)
    but that gives me no data and I know data is there.

    Thanks for any help you can provide.

    Krazy (Bill) Kasper

  2. #2
    Join Date
    Mar 2008
    Location
    Suburb of Chicago, Illinois
    Posts
    56

    Update

    UPDATE:
    I can use the following without getting an error but it still doesn't give me transactions >=7000

    =Sum(CSng(Nz([Quantity],"0")))

    Krazy Kasper

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I would say that you need to have a query set up with the transactions calculation in it. Then you can use that query in another query to interrogate data where transactions are > 7000.

    However, the SQL gurus here can probably do all that in one easy SQL statement, so it's probably worth waiting to see their response
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Mar 2008
    Location
    Suburb of Chicago, Illinois
    Posts
    56

    Thanks

    Thanks, I'll look into it while waiting for a response from the SQL guys.

Posting Permissions

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