variable table name

[pelajhia]

I am using an excel doc with multiple worksheets as a datasource. (Warned users that I couldn't promise anything, but I'd give it a try.)
The worksheets show up like tables in a database and the columns as fields in crystal. So far, so good.

I have set up a date parameter.
There are 12 of these pseudo-tables named JAN_, FEB_, MAR_, etc.
I need to be able to display data from one column in one worksheet - equivalent to one day's data when the report is run.
Is there some way to use the month and day from the date parameter to go to the right table and pull the right columns' data?
I have tried this, but I am getting an error that the {fieldname1} fieldname does not exist - is there some syntax that will get this last line to work?? Thanks!!

stringvar monthprefix;
stringvar daysuffix;

switch
(month({?BeginDate})=1, monthprefix:='JAN',
month({?BeginDate})=2, monthprefix:='FEB',
month({?BeginDate})=3, monthprefix:='MAR',
month({?BeginDate})=4, monthprefix:='APR',
month({?BeginDate})=5, monthprefix:='MAY',
month({?BeginDate})=6, monthprefix:='JUN',
month({?BeginDate})=7, monthprefix:='JUL',
month({?BeginDate})=8, monthprefix:='AUG',
month({?BeginDate})=9, monthprefix:='SEP',
month({?BeginDate})=10, monthprefix:='OCT',
month({?BeginDate})=11, monthprefix:='NOV',
month({?BeginDate})=12, monthprefix:='DEC');

switch(
day({?BeginDate})=1,daysuffix:='2',
day({?BeginDate})=2,daysuffix:='3',
day({?BeginDate})=3,daysuffix:='4',
day({?BeginDate})=4,daysuffix:='5',
day({?BeginDate})=5,daysuffix:='6',
day({?BeginDate})=6,daysuffix:='7',
day({?BeginDate})=7,daysuffix:='8',
day({?BeginDate})=8,daysuffix:='9',
day({?BeginDate})=9,daysuffix:='10',
day({?BeginDate})=10,daysuffix:='11',
day({?BeginDate})=11,daysuffix:='12',
day({?BeginDate})=12,daysuffix:='13',
day({?BeginDate})=13,daysuffix:='14',
day({?BeginDate})=14,daysuffix:='15',
day({?BeginDate})=15,daysuffix:='16',
day({?BeginDate})=16,daysuffix:='17',
day({?BeginDate})=17,daysuffix:='2',
day({?BeginDate})=18,daysuffix:='3',
day({?BeginDate})=19,daysuffix:='4',
day({?BeginDate})=20,daysuffix:='5',
day({?BeginDate})=21,daysuffix:='6',
day({?BeginDate})=22,daysuffix:='7',
day({?BeginDate})=23,daysuffix:='8',
day({?BeginDate})=24,daysuffix:='9',
day({?BeginDate})=25,daysuffix:='10',
day({?BeginDate})=26,daysuffix:='11',
day({?BeginDate})=27,daysuffix:='12',
day({?BeginDate})=28,daysuffix:='13',
day({?BeginDate})=29,daysuffix:='14',
day({?BeginDate})=30,daysuffix:='15',
day({?BeginDate})=31,daysuffix:='16'
);

shared numbervar array dayarray;
stringvar fieldname1;

fieldname1:=monthprefix+"_.F"+daysuffix;

redim preserve dayarray[recordnumber];

dayarray[recordnumber]:={fieldname1}

 

[FVTrainer]

What version of Crystal are you working with? And one point of clarification: When you say the worksheets show up as tables, isn't that only true if a range in the worksheet has been named? Or rather, isn't it the named ranges that are showing up as tables?

 

[pelajhia]

CR 9. I didn't go into the worksheet and set up any named ranges. I just tried to connect to it via crystal using the access/excel option and the way it displays the fields is as I've described. There are 12 worksheets in the file and these appear the way any regular table would appear in the crystal field explorer.

 

[FVTrainer]

I'm used to working with the ODBC driver rather than DAO, so I was expecting named ranges...

Anyway, I believe you can take a different approach and get what you want. Rather than see the data as 12 different tables, why not bring it in as one table and then use filtering to get the data you want? You can bring the data in as one table by going to the Database Expert and under the connection to the Excel file double-click on "Add Command". In the Command Editor, you would add a command along the lines of:

SELECT `Jan_`.`Field1`, `Jan_`.`Field2`
FROM `Jan$` `Jan_`
UNION ALL
SELECT `Feb_`.`Field1`, `Feb_`.`Field2`
FROM `Feb$` `Feb_`
UNION ALL
SELECT `Mar_`.`Field1`, `Mar_`.`Field2`
FROM `Mar$` `Mar_`
.
.
.

Using the UNION ALL statement, you would simply add all 12 months. Note that in the example above, Field1 and Field2 would be replaced by actual field names. If you haven't used Union queries before, the only major caveat is that each select statement must list the same number of fields and the fields must be listed in the same order.

Once you have all the data in one table, you should be able to use record filtering and/or conditional formulas to display the exact data you want.