Crosstab and Dates in Report 1

[perrymans]

My crosstab query returns all records for any date the information is entered. My problem is Reports.

I am using the following in the crosstab to rename the date columns:

Expr1: "d" & DateDiff("d",Date()-7,[SelQ_Transaction.TransactionDate])

And the textboxes in my report are based like:

=DateAdd("d",2,Date()-7)

For renamed field d2.

So it isn't the query that needs help, it is the report.

The solution I am using to get the dates into the report is the source of my issue.

Because the information is TYPICALLY workweek only, I am getting a lot of gaps in the data as empty weekend columns show up to ruin the data party.

Again, this is only in the report because of the solution I had to use to get the date fields to display.

Unless someone knows a better way to get crosstab column names to display in a report?

Thanks. Sean.

 

[dhookom]

Have you read faq703-5466 and changed the monthly increment to days?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[perrymans]

Yes I have, that is the same solution I used (shown above).

The problem is that solution produces a crosstab and subreport that includes weekend dates, which I need removed.

Like if I run it today I get:
5/22 5/21 5/20 5/19 5/18 5/17 5/16

But I need it to be:
5/22 5/21 5/18 5/17 5/16 5/15 5/14

Thanks. Sean.

 

[dhookom]

I'm not sure what would work best for you other than always grabbing 9 days since you know that 2 will be weekend. You could then hide the controls on your report that are Sat or Sun.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[perrymans]

But wouldn't that still leave blank spaces on the report since the textboxes are indivdually assigned?

Or perhaps I would sequence through in VBA and dynamically assign those textboxes?

My other thought was that whenever the report is run, a temp-table is made, read for the report, then deleted.

Hard to believe these are really the only options.

Thanks. Sean.

 

[dhookom]

You should always know either the start or end date. From this, you should be able to calculate the column label value to display. You should also be able to set a control source to something like:

txtD3
Control Source: =Choose(Weekday([some reference]), [d1],[d2],...)

It's a bit difficult to give you an exact answer since you haven't provided your SQL view or how you are specifying the date range.

I don't think you need any code.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[perrymans]

Absolutely, thanks for helping.

The (Date()-1)-7 portion is to start with yesterday (the report is run in the morning to review previous work).

TRANSFORM Sum(SelQ_Transaction.TransactionValue) AS SumOfTransactionValue
SELECT SelQ_Transaction.CategoryOrgID, Tbl_Task.TaskName
FROM SelQ_Transaction INNER JOIN Tbl_Task ON SelQ_Transaction.TaskID = Tbl_Task.TaskID
GROUP BY SelQ_Transaction.CategoryOrgID, Tbl_Task.TaskName
ORDER BY Tbl_Task.TaskName
PIVOT "d" & DateDiff("d",(Date()-1)-7,[SelQ_Transaction.TransactionDate]) In ("d0","d1","d2","d3","d4","d5","d6","d7");

I am trying to make the Weekday function work in here, no luck so far.

Thanks. Sean.

 

[dhookom]

I would first extend the Column Headings property to:

  In ("d0","d1","d2","d3","d4","d5","d6","d7","d8","d9")

Then your text box control sources would be something like:
left most text box:
Control Source: =Choose(WeekDay(Date()-8),[d1],[d0],[d0],[d0],[d0],[d0],[d2])
2nd text box
Control Source: =Choose(WeekDay(Date()-8),[d2],[d1],[d1],[d1],[d1],[d1],[d3])

I'm not sure on all the different "mappings" of the proper columns to the fields so you need to play with it.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[perrymans]

This is the second time now that I have been defeated.

I can't do it, I don't get how Choose(Weekday) is returning anything useful, I don't know how I would even change the numeric output of Weekday() back into an actual date, I don't know how to pass the date range, and everything I try doesn't even come close.

I don't suppose you have a functioning example by chance? I think I am going to un-normalize the database because this is ridiculously difficult.

Thanks. Sean.