Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2002
    Location
    Ann Arbor, MI
    Posts
    47

    Red face Unanswered: Dcount on a report based on a query

    I have a report based on a query "QC_LOG_DATE_SORT". This Query is sorted by dates you enter upon opening. I am trying to count the number of rejects "R" that appear in the "Accept/Reject" field. I have placed the following in the Control Source for a Text Box in the Footer of the Report.

    =DCount("[Accept/Reject]","[QC_LOG_DATE_SORT]","[Accept/Reject] = R ")

    It returns this: #Error

    I have also tried:
    =DCount("[Accept/Reject]","[QC_LOG_DATE_SORT]","[Accept/Reject] = 'R' ")
    -and-
    =DCount("[Accept/Reject]","QC_LOG_DATE_SORT","[Accept/Reject] = R ")
    -and-
    =DCount("[Accept/Reject]","QC_LOG_DATE_SORT","[Accept/Reject] = 'R' ")

    Please help! Thanks

  2. #2
    Join Date
    Sep 2002
    Location
    Coventry, Warwickshire, UK
    Posts
    74
    You definitely need the 'R' if [Accept/Reject] is a text field.

    =DCount("[Accept/Reject]","QC_LOG_DATE_SORT","[Accept/Reject] = 'R' ")

    Apart from that, your expression is good code and should work.

    The next thing to check is the query. Is it returning the results you expect? Does it return the [Accept/Reject] field?
    Roger Hampson
    XI - ecs (UK) Ltd

  3. #3
    Join Date
    Sep 2002
    Location
    Ann Arbor, MI
    Posts
    47
    Yes, the query works fine. I wonder if using [braketed] Criteria (that i use as a date filter) poses a problem? I have tried using this function against a table and I still get the same #Error.

    p.s. I am using Access 97 SR-1. Are their any important service releases or updates that might be affecting this?

  4. #4
    Join Date
    Sep 2002
    Location
    Coventry, Warwickshire, UK
    Posts
    74
    Can you explain your first paragraph a bit more. What do you mean by

    "I wonder if using [braketed] Criteria (that i use as a date filter) poses a problem?"

    What does the SQL in query QC_LOG_DATE_SORT look like?
    Roger Hampson
    XI - ecs (UK) Ltd

  5. #5
    Join Date
    Sep 2002
    Location
    Ann Arbor, MI
    Posts
    47
    What I mean is this: in my query I filter by Date using a Between statement in the Criteria (WHERE) field:

    Between [Enter Begin Date] And [Enter End Date]

    The SQL code is:

    SELECT [QC Log].[Part#], [QC Log].Quan, [QC Log].DateQC, [QC Log].[INSR#], [QC Log].[Accept/Reject], [QC Log].Vendor, [QC Log].DateReceived, [QC Log].TimeRecieved, [QC Log].TimeQC, 24*([DateQC]-[DateReceived]) AS hoursdays, Hour([TimeQC])-Hour([TimeRecieved]) AS hourshour, Minute([TimeQC])-Minute([TimeRecieved]) AS Expr2, [Expr2]/60 AS hoursminute, [hoursdays]+[hourshour]+[hoursminute] AS TotalTime
    FROM [QC Log]
    WHERE ((([QC Log].DateQC) Between [Type the begin Date] And [Type the Ending date]))
    ORDER BY [QC Log].[Part#], [QC Log].DateQC, [QC Log].Vendor;

    As an additional note, I cannot get any Domain Aggregate Functions to work anywhere in my database (on Tables or Queries).

    All help or thoughts appreciated! Thanks! -Keith

  6. #6
    Join Date
    Sep 2002
    Location
    Coventry, Warwickshire, UK
    Posts
    74
    I think your problem is with the SELECT statement. To check if that is so, try running this query

    SELECT [QC Log].[Part#], [QC Log].Quan, [QC Log].DateQC, [QC Log].[INSR#], [QC Log].[Accept/Reject], [QC Log].Vendor, [QC Log].DateReceived, [QC Log].TimeRecieved, [QC Log].TimeQC
    FROM [QC Log]
    WHERE ((([QC Log].DateQC) Between [Type the begin Date] And [Type the Ending date]))
    ORDER BY [QC Log].[Part#], [QC Log].DateQC, [QC Log].Vendor;

    The calculations you are trying to do in the SELECT statement would normally be done in report controls, if you are using the query for a report. I don't think the query will resolve Minute([TimeQC])-Minute([TimeRecieved]) AS Expr2 to allow you to use it in [Expr2]/60 AS hoursminute until the query has been completed. My understanding is that the assignment of the new name, eg Expr2, is not done until the query has been completely resolved. Therefore it can't compute [Expr2]/60.
    Roger Hampson
    XI - ecs (UK) Ltd

Posting Permissions

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