Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    data type mismatch in criteria expression (2000 al

    Using Access 2000

    This is too long a message to post, so I have attached a Microsoft Word document which explains the difficulty.

    It has to do with a "data type mismatch in criteria expression" problem when trying to get totals from columns in a report.

    Thanks.

    Tom
    Attached Files Attached Files

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

    Re: data type mismatch in criteria expression (2000 al

    The Format function results in a text value. You can see this in the query results: text values are left-aligned and numeric values are right-aligned. Each of the columns that is defined using the Format function is left-aligned.
    You shouldn't use the Format function if you want to use values in calculations. Instead, set the Format property of the column in a query, or of the control bound to the column on a form or report.

  3. #3
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: data type mismatch in criteria expression (200

    Hans
    Thanks for your reply. I went back to the 2 queries upon which the Union query is built, and set the columns there so that the formatting of the desired columns is "currency."

    The Union query SQL now is
    <code>SELECT QuoteDate,ProjectID,FullName,TotalMaterials AS Materials,'' AS Labour,PSTCost AS PST,GSTCost AS GST,GrandTotal AS [Total Quote Cost],ProjectNbr
    FROM qryQuoteTotalsMaterials
    UNION SELECT QuoteDate,ProjectID, FullName,'',TotalLabour,'',GSTCost AS GST,GrandTotal AS [Total Quote Cost],ProjectNbr
    FROM qryQuoteTotalsLabour;</code>

    However, when I run the Union query, the values revert to text...except for the two columns GST and [Total Quote Cost] which always have values...and I can't change the columns to currency in the report.

    (What I originally thought was desirable was to put the formatting at the highest level query, which in this case would be the Union query)

    I am attempting to construct a report so that Derek can get the totals of Quotes during a selected time period.

    What I have to allow for is for Derek to have a Project where no Materials are to be quoted, and also where no Labour is to be quoted. Additionally, there is no PST on Labour, but there is GST on both Materials and Labour, except in cases where the customer is exempt from GST.

    Tom

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

    Re: data type mismatch in criteria expression (200

    Use either Null or 0 instead of <code>''</code>. The value <code>''</code> is a string (text) value, forcing the entire field to become text.

  5. #5
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: data type mismatch in criteria expression (200

    Hans
    Yep, changing '' to 0 made all the difference in the world.

    I was just using the '' as an empty place-holder because there are differences in the number of columns in the two queries behind the Union query, but hadn't realized the implications of doing so.

    I wish I had made that post earlier. It would have saved me a lot of fiddling around.

    Thanks a lot.

    Tom

Posting Permissions

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