Cross Tab Column Headings...

[mhoctober]

Experts...



my Access database keeps track of orders for frozen food items.

I have a crosstab query that groups the sale of items by delivery date - with the ultimate aim of it showing me the stock remaining level of item(s) as the week progresses, via a report or a form. We take orders throughout the week from customers.

The cross tab creates a date column for each day of the week (based on paremetres input via a form), and in this column shows me the item sales.

I understand the concept of fixing column headers and have created column headers of Day1, Day2, Day3, Day4, Day5 (one weeks worth of data is sufficient for my purposes just now).

Knowing that I have total item sales data grouped by DeliveryDate could anyone help me with an expression that will consistently produce output column headings of ...

Day1, Day2, Day3, Day4, Day5

for ANY set of 5 consecutive dates that are passed to the query and it finds data for?

I have been playing around with


ColHead : "Day" & DateDiff("d",Format([orderDeliveryDate],"0"),Date())

and I think I'm getting close????

Many thanks...

Mike

[weekend00]

It looks like you got it already? I just don't think you need to format the orderdeliverydate. this is enough:"Day" & DateDiff("d",orderDeliveryDate,Date())

what is you question?

[mhoctober]

Weekend00....thanks for the reply. Either the issue runs a little deeper than I have explained or else I maybe missing something very obvious!!!

If I run the query on 11/9 with the expression

"Day" & DateDiff("d",orderDeliveryDate,Date())

where the date parametres from the form are Wed 8/9 and Fri 10/9 I get Day1, Day2 and Day3 as output column headers - albeit the data in these columns is invalid in that Day 1 = Friday and Day 3 = Wednesday.

If I then run the query again on the same day with the input parametres of 15/9 and 17/9 I get the following column headers...

Day-4, Day-5 and Day-6.

What I am trying to achieve is a situation whereby no matter what date I run the query or what dates are passed to it as parametres I always get Day1, Day2, Day3 etc as output column headers with data in the Day1 column that relates to the first date passed to the query, data in the Day2 column that relates to the next date etc....

Thanks again for the reply.

Regards

Mike

[weekend00]

You'd better post you query here, or a simplified database if possible.
that will be much easier for us to see what happened