Basing a report on an updated cross-tab query

[Lotharia]

I have a cross-tab query based on a table that I need to update daily. (The
main fields are totals by date.)

I have a report based on that query that I need to run daily following the
table update.

I get an error message as soon as a new date appears in the table and query.
The report seems to keep the dates hard-coded.

What can I do to remedy this situation?

 

[Daryl S]

Lotharia -

The only thing I know is you can do a SELECT * in your second query. If you
list fieldnames, then as the crosstab changes, you have to manually change
the second query.

 

[Robert_DubYa]

The problem you are running into is that your form is looking for a specific
column name. What I have done in the past to correct this problem is to base
your form off of a select query with mulitple columns and an iif statement.
This will keep the name of the column static.

example if you were wanting to sum a qty by date for three days:

Column 1

Today:sum(iif([YourDate]=date(),[YourQty],0)

Column 2

TodayPlus1:sum(iif([YourDate]=date()+1,[YourQty],0)

Column 3

TodayPlus2:sum(iif([YourDate]=date()+2,[YourQty],0)


As far as your form goes just make custom labels for each text box IE
date(), date()+1.

I hope this makes sence.

Robert