sequential dates in a report? 1

[CrashDome]

Hmmm... been banging my head over this one!

I have a table for a list of jobs. I created a report for our production managers that lists these jobs grouped by SHIP DATE. They can select [Start Date] and [End Date] to filter it. The jobs are then listed per page by DAY. Example: one page might list jobs that ship on January 20th and the next January 21st. However, to my dismay they express that they needed blank boxes to fill in info while on the plant floor. I proceeded to add 10 extra "Blank" boxes at the footer of the group and they were happy until.... no jobs were scheduled to ship on one particular day and the page was never printed. I asked them what was the problem... and they want a page printed even if there isn't a job, just so they can have those extra blank boxes.
Ugh,... ok.. the actual question:How can I change my report to group instead of by SHIP DATE to grouping by each day from [Start Date] to [End Date] which are parameters of the query from which the report is generated. I hope this is enough info? Anybody got a solution?

 

[demoman]

Very easy solution, especially for an MRP system. You should have a manufacturing calander table in your system. If not, create it. You will find many uses for it. It is usually ran annually, and covers 24 months. There is nothing to say it cannot be ran for different time intervals, though.

You will need to write a little code to populate the table.
The minimum you need is 1 column, which has the date, for each work date.

For the report you are working on, you will change your query to include your calendar dates.

If you need further clarification, or code help, let me know.

Steve
stevem@nuprecon.com

 

[CrashDome]

Ah... I think I understand. In other words, I need a table with each date as a field. Perhaps if Manufacturing only looks three weeks ahead, I will need to create a table with each day of those three weeks every time they open the report as a minimum. OK.. this helps... thank you.

If I have a create table query... with 2 parameters (Start and End) how do I increase each day by 1 so the records fill in the appropriate value for each day. I'm not on the computer with my database, so I can't look into it yet.

 

[demoman]

Here is code that will populate your mfg calendar with dates. Let me know if you have questions

Public Sub Add_Mfg_Dates() 'creates mfg calendar for 2002

Dim dbs As Database
Dim datEndDate As Date
Dim datStartDate As Date
Dim strDayOfWeek As String
Dim strSQLCmd As String

Set dbs = CurrentDb
datStartDate = #1/1/2002#
datEndDate = #12/31/2002#


Do While datStartDate <= datEndDate

strDayOfWeek = Format(datStartDate, &quot;dddd&quot

If strDayOfWeek <> &quot;Sunday&quot; And strDayOfWeek <> &quot;Saturday&quot; Then 'bypass weekends

strSQLCmd = &quot;Insert into ManufacturingCalendar (MfgDate) values (&quot; & &quot;#&quot; & datStartDate & &quot;#)&quot;
dbs.Execute strSQLCmd 'insert row of data

End If

datStartDate = DateAdd(&quot;d&quot;, 1, datStartDate) 'increment date
Loop

End Sub