Basing a report on an updated cross-tab query

  • Thread starter Thread starter Lotharia
  • Start date Start date
L

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?
 
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.
 
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
 
Back
Top