Results 1 to 5 of 5
  1. #1
    Newbie11 is offline Advanced Beginner
    Windows 2K Access 2003
    Join Date
    Dec 2011
    Posts
    41

    DCount

    I've been struggling with this for a while and finally decided I needed some help. I have a report "Order Info" that is filtered by a form filter "OrderFilter".

    The filter has fields for OrderFrom, Order To, and txtStart and txtEnd. The report opens based on the information entered in the filter and works fine giving me the information that I want (which for this report is how many orders have been shipped).



    What I am trying to excute is a DCount that lets me determine specifically how many orders have been made that include the exact ShipTo and ShipFrom fields. That is, out of all the various combinations of where an order can be shipped to and from I want to know how many orders have been made to the selected ShipTo field from the selected ShipFrom field. I need this information to be able to display an order status percentage in the report.

    My curent dcount is:
    =DCount("[OrderID]","qry_OrderStatus","[ShipTo] ='" & [ShipTo] & "' And [ShipFrom] ='" & [ShipFrom] & "' And [Date] BETWEEN [Forms]![OrderFilter]![txtStart] And [Forms]![OrderFilter]![txtEnd] ")

    Regardless of what combinations I come up with this number is always lower than what the actual value is. I've tested the output against going into qry_OrderStatus and filtering out the data to match what was entered in the OrderFilter.

    The form, report, and query have all executed nicely and given the proper results. I'm not sure where the disconnect is coming with the DCount.

  2. #2
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    This appears to be a classic case for extensive debugging; the technique I would use is to start with a 'bare bones' WHERE clause, test that, and then gradually add terms testing as I go along until the result did not match my prediction.

    OK, here are some other thoughts.

    DCount("[OrderID]", ... will not include instances where OrderID is null. Does this occur in your db?

    Is it possible that either txtStart or txtEnd is null? Are you sure they are proper dates?

    [Date] is a very bad choice for an attribute name. There is a function called Date that returns the system date and use of the same name may cause conflicts. However since the name is bracketed I don't think a conflict will occur here.

    I would use parentheses to clarify the different terms of the WHERE clause. However once again I don't think this is necessary in your case.

  3. #3
    amrut is offline Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2012
    Location
    Dubai
    Posts
    614
    Are you missing the delimeter "#" for date ?
    =DCount("[OrderID]","qry_OrderStatus","[ShipTo] ='" & [ShipTo] & "' And [ShipFrom] ='" & [ShipFrom] & "' And [Date] BETWEEN #" & [Forms]![OrderFilter]![txtStart] & "# And #" & [Forms]![OrderFilter]![txtEnd] & "#")

  4. #4
    Newbie11 is offline Advanced Beginner
    Windows 2K Access 2003
    Join Date
    Dec 2011
    Posts
    41
    As was suggested I rebuilt the DCount piece by piece and what triggers the problem is [ShipTo] ='" & [ShipTo] & "' And [ShipFrom] ='" & [ShipFrom] & "'. When I add these two criteria invidually or in tandem the result is always skewed.

    There won't be any records that do not have and OrderID and txtStart and txtEnd function as they should as I receive the correct amount of records when I drop the Dcount to :
    =DCount("[OrderID]","qry_OrderStatus","[Date] BETWEEN [Forms]![OrderFilter]![txtStart] And [Forms]![OrderFilter]![txtEnd]")

    I also added the date delimeters but that did not make a difference

  5. #5
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    So it's [ShipTo] and [ShipFrom] that are causing the problem. I notice they are both text fields/attributes so this might be a clue. I have at the back of my mind that I read somewhere that the SQL engine comparison is different from the Access/Jet comparison. Could this explain differing results?

    First try fully qualifying the references to the form fields such as [Forms]![OrderForm]![ShipFrom] or whatever. Do you have more than one [ShipFrom] field? You mention a report; is there also a [ShipFrom] field on the report?

    If it's a data problem then spaces, capitals and non-printable characters are all candidate culprits. You need to tediously test every combination you can think of to determine if they are all wrong or not. If only some are wrong then what is the commonality between the erroneous combinations? Copy the db and work on the copy since you may want to delete data to make it more managable.

    Good luck.

    Regarding dates: dates specified as strings need the date delimiter hash character and then they must be in American mm/dd/yyyy format. In your case, as long as the form fields are specified as date fields then whatever the visible format, Access stores the dates behind-the-scene as numbers and your syntax works.

    Let us know how you get on.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. DCount
    By Ray67 in forum Queries
    Replies: 2
    Last Post: 06-26-2012, 10:48 AM
  2. DCount
    By nsteenhaut in forum Queries
    Replies: 2
    Last Post: 10-04-2011, 05:00 PM
  3. DCount and If
    By Madmax in forum Access
    Replies: 3
    Last Post: 07-28-2011, 06:53 AM
  4. DCount and SQL
    By DSTR3 in forum Queries
    Replies: 3
    Last Post: 12-06-2010, 03:07 PM
  5. Help with Dcount
    By tozey in forum Programming
    Replies: 1
    Last Post: 08-10-2010, 10:53 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums