Results 1 to 5 of 5
  1. #1
    rmerkle is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    2

    Problem counting number of 'special' days between dates.

    Here is what I am trying to do.



    I have 2 sets of data that i am trying to combine. One set is order lead time and the other is the number of orders shipped each day. I want to set up a query that will tell me the number of BUSINESS days from when an order is received to when it is shipped. The issue is that business days are random, and are not easy to code in (I also do not want to have to maintain a table of business days).

    What i have done so far is create 2 queries. The first query creates a list of business days by counting any day where more than 50 orders where shipped as a business day. The second query is simply a list of all orders where the shipped day is linked to the business days (i dont care about the lead time on orders that dont ship on business days).

    My query to calculate lead time includes this:
    DAYS: DCount("*","qryOrderShip","[DATE] between #" & [ORDER] & "# AND #" & [SHIP] & "#")

    This works to calulate the unique operating days between order and shipment, but the problem is the speed. It takes roughly 2 minutes to return 8000 records, but the real problem is that attempting to make a query off of that query simply crashes my computer. Trying to export the information via application or Excel crashes those programs as well.

    Is there a better way to do this? I am starting to think DCount with a large number of records is a bad idea. Any help would be greatly appreciated.

  2. #2
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    Try using DateDiff() to calulate the duration between the two dates in one column then sum this column.

    Elapsed:DateDiff("d",[Order],[Ship])

    David

  3. #3
    SpotoR1 is offline Novice
    Windows Vista Access 2007
    Join Date
    Mar 2009
    Location
    Japan
    Posts
    6

    Also

    Any time you close you should always remove your records. I can't think of the process because I dont have 2007 on my home computer but you can google this process I am sure.
    If you dont do this the temp records are kept and when you deal with a lot of records you should always clear these.

    I hope this helps a little.

    ray

  4. #4
    rmerkle is offline Novice
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    2
    Quote Originally Posted by dcrake View Post
    Try using DateDiff() to calulate the duration between the two dates in one column then sum this column.

    Elapsed: DateDiff("d",[Order],[Ship])

    David
    Thank you for your reply. This would definately make things easier, but i cannot use it since i need to count only the days that are listed in qryOrderShip and nothing else. Since that is the only location with that information, i HAVE to refer to it at some point.

  5. #5
    dcrake's Avatar
    dcrake is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Aug 2009
    Posts
    435
    If you include this expression in your query it will work, just try it.

    David

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

Similar Threads

  1. Need to delete special characters
    By tlrutledge in forum Queries
    Replies: 1
    Last Post: 08-23-2013, 03:10 AM
  2. Hi. I need very, very special help for Ms Access!
    By accessnotknow in forum Access
    Replies: 0
    Last Post: 06-12-2009, 12:21 PM
  3. less than 180 days old
    By techexpressinc in forum Queries
    Replies: 2
    Last Post: 06-04-2009, 04:29 PM
  4. Special Characters
    By orgelizer in forum Access
    Replies: 0
    Last Post: 03-20-2007, 08:24 PM
  5. Total no of days in a month
    By wasim_sono in forum Forms
    Replies: 4
    Last Post: 10-15-2006, 01:05 PM

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