Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2011
    Location
    Baltimore, MD
    Posts
    22

    Unanswered: Date Serial Issue

    I have a table that shows allocated services based on a start and end date.
    I want a query to show a row for each month that the service is going to be provided or not.
    The query also has another table with the start and end dates of every month for the next 10 years.
    The problem is that when a service has a start/end date in the middle of a month, that month won't be captured in this query.
    Here's that I tried but this is not working:
    Between DateSerial(Year[tblservice.startdate]), Month([tblservice.startdate]), 1) and DateSerial(Year[tblservice.enddate]), Month([tblservice.enddate]), 1)
    Any suggestions?

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    If you want the service to show in all months the service is provided (not just the starting month say) then I think you need an OR ie.
    Code:
    Between DateSerial(Year[tblservice.startdate]), Month([tblservice.startdate]), 1) OR DateSerial(Year[tblservice.enddate]), Month([tblservice.enddate]), 1)
    Dose that help?

    If it was me, based on the given information, I would only be comparing the Year() and Month() of each start/end date with the Year/Month in question (ie I would not have a table of Month Start and End Dates, just years and months!)

    If fact I probable would have a Year table and a Month tabel (ie one column of 1 to 12) and generate the Month/Year tabe using a query with a Cartesian join (ie. SELECT tblYear.*, tblMonth.* FROM tblYear, tblMonth)

    But that's just me!


    MTB

  3. #3
    Join Date
    Nov 2011
    Location
    Baltimore, MD
    Posts
    22
    I'm a little new to this so your method is a little unclear to me.
    If I have a service that is allocated from 7/5/11- 6/27/12, I would want to see 12 records in my query for that client [7/1/11-7/31/11, 8/1/11-8/31/11...)
    So how would the cartesian join help me do that?

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    There are no doubt other ways of doing this but this seem to work.

    Create a table tblYear with a field named ThisYear (for rexample) and enter the years you want to list services for.

    Create a table tblMonth with a field named ThisMonth (or similar)

    Create a stored query called qryYearMonth with an sql =
    Code:
    SELECT tblYear.ThisYear, tblMonth.ThisMonth FROM tblMonth, tblYear ORDER BY tblYear.ThisYear, tblMonth.ThisMonth;
    Then using this qery and your 'Service' table create a query like this
    Code:
    SELECT qryYearMonth.ThisYear, qryYearMonth.ThisMonth, tblService.StartDate, tblService.EndDate
    FROM tblService, qryYearMonth
    WHERE (((Year([StartDate]))=[qryYearMonth].[ThisYear]) AND ((Month([StartDate]))<=[ThisMonth])) 
    OR (((Year([EndDate]))=[qryYearMonth].[ThisYear]) AND ((Month([EndDate]))>=[ThisMonth]))
    ORDER BY qryYearMonth.ThisYear, qryYearMonth.ThisMonth;
    This will result in a line for each month between the the start month and the end month inclusive.

    If anyone has any less complex/more conventional solutions then I would be very interested.

    MTB

  5. #5
    Join Date
    Nov 2011
    Location
    Baltimore, MD
    Posts
    22
    But what goes into the tblThisMonth?
    Is it a start date, end date or just a month number?

  6. #6
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Quote Originally Posted by shuashef View Post
    But what goes into the tblThisMonth?
    Is it a start date, end date or just a month number?
    The numbers 1 to 12

Posting Permissions

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