Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Columbus, Ohio, USA
    Posts
    286
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Quirk (Access 2000)

    Good Morning All:

    I'm trying to create a query with the following fields: ProductId,
    rptMonth: DateSerial(Year([OrderDate]),Month([OrderDate]),1), and Quantity. It is a Totals query with the group by fields being ProductID and rptMonth. The Quantity field is "sum". Data types are ProductID - Integer; OrderDate - Date/Time; Quantity - Integer. When running the query I receive the following message: Data Type Mismatch in criteria expression. The kicker is that there is no criteria expression in any of the 3 fields. Any and all suggestions are appreciated. TIA.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Query Quirk (Access 2000)

    A query like that should work without problems. Could you switch to SQL view, copy the SQL statement that is displayed, and paste it into a reply?

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Columbus, Ohio, USA
    Posts
    286
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Quirk (Access 2000)

    Thanks for the reply. Here is the SQL:

    SELECT [Order Details].ProductID, DateSerial(Year([OrderDate]),Month([OrderDate]),1) AS rptMonth, Sum([Order Details].Quanity) AS Total
    FROM [Order Details]
    GROUP BY [Order Details].ProductID, DateSerial(Year([OrderDate]),Month([OrderDate]),1)
    WITH OWNERACCESS OPTION;

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Quirk (Access 2000)

    A quick test give me the same error if there are dates (your OrderDate) that are empty.
    The combination of DateSerial and Total are not working with Null or empty dates.
    Is this the case in your table ?
    Francois

Posting Permissions

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