Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2007
    Posts
    15

    Question Unanswered: days count prob.? plz Help

    Dear all
    now i have a db for car renting company
    i store for every customer two dates "begin date" & "end date" , mean when it begin to rent and when it finish the renting
    now i want to make a report how many days such cust rent a car in a certain month?

    for example begin date 1/4/2007 to end date 1/7/2007 OK
    how many days in month 5 he rent the car ??
    if i query by begin or end day by month 5 ,i will have no result
    since the month 5 "May" lay between the begin & end day

    did any one understand me
    how to make it in a smart automatic way
    or i will need a complex query and VBA to did that !! !

    thanks all
    bye

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT DateDiff(dd, [begin date], [end date])
    FROM   MyTable
    Does this do what you want?
    George
    Home | Blog

  3. #3
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I'm afraid that the easiest way to do this is the hard way. In VBA, build a temp table record by record, for each date the customer rented the car. In your example, the customer's data will occupy 90 or so records, between April 1 and July 1. After you build this table, run a query on the temp table any which way you want with the confidence that it won't miss any dates, because it's querying the temp table, not the original table. Just be sure you have enough info, structure-wise, in the temp table, so you can make a meaningful JOIN to the main table in your query.

    Sam

Posting Permissions

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