Generating a consecutive date for calendar report

[azalealee]

Hi

I'm trying to design a calendar report for a Work Scheduling Access 97 database, but I'm having some difficulties.

I would like the report to have a separtate row for each day, the name of the people doing the work as columns and the work descriptions as entries.

The user inputs the date range of the report from a form as txtFromDate and txtToDate.

The first problem I'm having is generating a consecutive date for each row starting from txtFromDate, even if there are no entries for that date.

The second problem is I don't know how to get columns for each person. (I've tried setting multiple columns in Page-Setup, but I haven't got it to work very well).

Can anyone point me in the right direction? Or even be able to email me a similar calendar report that they've done?

Thanks in advance. Azalea

 

[dhookom]

There are several calendar type reports in a sample at

http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

In some of these examples you would need to have at least one scheduled event per week.

If you want to create a resultset that has each day in it, you would be best of creating a simple table of dates that has each date in it. You can then add this to your query and join the date fields. Edit the join properties so that every row is selected from the table of dates even if there is no match in the events table.

A crosstab query would place each person as column headings and dates as row headings. The descriptions would be the Value.

Duane
MS Access MVP

 

[azalealee]

I tried creating a crosstab query by specifying the new table All_Dates as the rows, Allocated_To as columns and Description as values.

Unfortunately I can't get the query to run because I use many Immediate If (IIf) statements in my original query. These IIf statements evaluate user selected values from controls on form F_Filter as criteria.

I get the message "The Microsoft Jet Engine database does not recognise [Forms]![F_Filter]![cboName] as a valid field name or expression".

Is there another way beside crosstab queries?

Thanks for your help.

 

[dhookom]

Why don't you fix the crosstab query? You can select Query|Parameters and enter:
[Forms]![F_Filter]![cboName] Text
In addition to the calendar type reports, there is also a sample zip file of crosstab reports.

Duane
MS Access MVP

 

[azalealee]

Thanks. Did what you said, and it got rid of those messages.

I'm now getting the message "You tried to execute a query tht doesn't include the specified expression 'Description' as part of an aggregate function."

Am I able to set Description as a value in a crosstab if it isn't an aggregate value (ie sum, count etc)? Description is a text field.

 

[dhookom]

Use First or Max or Min or Last

Duane
MS Access MVP

 

[azalealee]

I get the error message "The expression is too complex to be evaluated..."

Perhaps it isn't able to find the First/Min/Max/Last of a text field?

Maybe I should have mentioned that there are many Descriptions for each date and I need each of them listed.

Thanks for your patience.

 

[dhookom]

It would have helped if you had revealed more about your data and requirements. A standard query will allow only one value. A standard query is not too complex. You probably have something else in your query that creates this condition.

Is your description field a memo type or text?

To get multiple values, you could search google groups on my name and concatenate to find a function that would concatenate all the descriptions together into one expression. However, it seems we may be working on a very long thread.

Duane
MS Access MVP

 

[azalealee]

Description is a text field.

I've created something that might work for me for the time being - the format is a bit dodgy, but at least the info is in the right places.

I created a form based on table All_Dates and put a subreport into the detail that has a header for Allocated_To and the Descriptions as detail. The two reports are linked by date.

I've tried to create the columns for each person through page setup-columns. The format is a bit messy, especially becuase there are varying numbers of people for different criteria....but it works.

Thanks for your help.