Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Mar 2002
    Location
    North Carolina
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find Missing Days (2000 SR1)

    Hi all,

    I have created a table with a field for a name and a field for a date. I need to report on the which days of the month I am missing for each person within a specified date range. Anybody done anything similar?

    Thanks much,
    Cynthia

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find Missing Days (2000 SR1)

    I would create a temporary table with one field, a date field, adding a record for each day between the two dates.
    Use this sql to find the missing dates for one person :

    SELECT tblTempDate.ControlDate
    FROM Table RIGHT JOIN tblTempDate ON Table.Date = tblTempDate.ControlDate
    WHERE (((Table.Date) Is Null) AND ((Table.Name)="PersonsName")) OR (((Table.Date) Is Null) AND ((Table.Name) Is Null));

    This suppose :
    The temporary table is named tblTempDate with one field ControlDate
    Data table is named Table with 2 fields : Name and Date.
    You want the missing dates for someone named PersonsName
    Francois

  3. #3
    New Lounger
    Join Date
    Mar 2002
    Location
    North Carolina
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find Missing Days (2000 SR1)

    I will try it now. Thank you, you have no idea how much time and coffee you saved me!!

  4. #4
    New Lounger
    Join Date
    Mar 2002
    Location
    North Carolina
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find Missing Days (2000 SR1)

    Any way to make this step through all the people in the table one by one to create a listing of which days are missing for which person? In a module perhaps? Also, anyone know how to create a calendar form that would create a calendar for each person and cross off the days that are in the table?

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find Missing Days (2000 SR1)

    For a Table:
    In a module, create a recordset that return all the names in the table.
    For each name, create a recordset with the sql from previous mail.
    For each record in this recordset, add a record in a new table with the name and the date.
    For a report:
    Create a report with in the detail the name and a subreport based on the SQL of previous mail.
    For a calendar form, let me some time to think. <img src=/S/newbrain.gif border=0 alt=newbrain width=21 height=22>
    Francois

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find Missing Days (2000 SR1)

    Here attached, a sample of a calendar where the back color of the missing dates are changed to red.
    It may need some fine tuning but you have something to start.
    Attached Files Attached Files
    Francois

  7. #7
    New Lounger
    Join Date
    Mar 2002
    Location
    North Carolina
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Find Missing Days (2000 SR1)

    This is fabulous,a million thanks! The calendar form needed very little tweaking and it is working beautifully! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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