Get correct record - help needed

[twitchie]

I have refined my earlier question to the group. I set up a query that has all the fields of my table. I have a report that draws the records from this query. The first set of records come thru just fine. However, the report needs records for 7 days (all days of the week). The report needs to distinguish between record sets based on the date. The date is automatically entered on the form. Another field I call FirstDate is entered by the user on the first day of the week. FirstDate tells the report on what date to start counting the days' dates. All that work fine. But when I tell the report to display ActualSales, how do I get it to display ActualSales for each of the 7 dates of the week? Every expression I build either displays the ActualSales from the first entry on all 7 days or gives me an error message on the report. Hope that make sense. Many TIA!

James

 

[Rich]

>[FirstDate] in the criteria section of your date field, but why do you need users to enter the first day of the week? Access can work that out

 

[twitchie]

I don't know how to get Access to work out the first day of the week so I had the user input it. Having Access work it out would be a much safer and more reliable method. How do I go about that? Also, I put [FirstDate] into the criteria field of [Date], but date fields on my report (calculated from [FirstDate]+1, [FirstDate]+2, etc) now say '#Error' and the ActualSales fields are blank. Where did I mess up? Thanx!

James

 

[twitchie]

Oops. Missed the '>' sign. The Date fields are fixed now, but all the ActualSales fields give the same figure - the one from the first record I added for testing. Thanx.

James

 

[twitchie]

Ok. Rewind. I should have mentioned this earlier. I am not using the traditional design for my report. Ususally, a new reord in added under the previous one. In that design, it is working. Each record has generated a new page with the data. However, I want each record side-by-side for the entire week (all 7 days). That's where I'm running into trouble. How do I accomplish that? Thanx!

James

 

[Rich]

Use a CrosstabQuery, you'll have to enter the column headings, Monday, Tuesday etc

 

[twitchie]

I've never used a CrosstabQuery. I got one in the works, but exactly where do I create the column headings (days of the week) Access is asking for? Thanx for your help!

James

 

[twitchie]

Instead of just creating a box and writing the day in on the report, should I have a SelectBox or something?

 

[twitchie]

ListBox, I mean.

 

[Rich]

The Columns property is on the PropertySheet for the query, if you want users to enter a start date you can use an unbound textbox on a pop up form, where you can validate the Day, or users can select the start date from the built in calcntrol.
You'll have to define the parameters for the crosstab to work, search here for further help.