I'm running Seagate Crystal Reports 8 and I have a report that I change the database name on every month (i.e. the database for last month is “Error Report 08/01/2004” and this month is “Error Report 09/01/2004”.) Currently I use a parameter field that the database date is hand entered into but to reduce the margin of error for incorrect dates getting in there I would like to use the database name. Is this possible?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Displaying location information 1
- Thread starter Thines
- Start date
-
1
- #2
synapsevampire
Programmer
To reproduce your output using a derived forum, use:
“Error Report "& minimum(monthtodate)
To get last months, use:
“Error Report "& minimum(lastfullmonth)
You might use a SQL Expression to return the database name, but that output would depend upon the type of database and connectivity used, which for some unknown reason you've deemed unimportant to share.
-k
“Error Report "& minimum(monthtodate)
To get last months, use:
“Error Report "& minimum(lastfullmonth)
You might use a SQL Expression to return the database name, but that output would depend upon the type of database and connectivity used, which for some unknown reason you've deemed unimportant to share.
-k
- Thread starter
- #3
The report that I'm using reads from a Microsoft Access Database while joining to several unique SQL databases. It's not the way I would prefer to do it but for the time being it's how I have to do it. The tables themselves are produced twice monthly and not always on the same date - they are depedent upon another department's process and for auditing purposes the dates must remain consistent. Here is an example of the actually table names within the Access database:
Error Report 05/04/2004
Error Report 05/19/2004
Error Report 06/02/2004
Error Report 06/16/2004
Error Report 06/29/2004
Error Report 07/20/2004
Hopefully this clarifies things a bit more.
Error Report 05/04/2004
Error Report 05/19/2004
Error Report 06/02/2004
Error Report 06/16/2004
Error Report 06/29/2004
Error Report 07/20/2004
Hopefully this clarifies things a bit more.
synapsevampire
Programmer
A bit, thanks.
How does Crystal know which table to use each time?
If you're changing it manually in the report, you might manually change it in the report.
Do you want the database name or the table name, the first post speaks of databases, the second of tables.
If the database, than it sounds like it's always an Access database so you can use a hardcoded value. If you mean the name of the linked SQL Server database containing a table, you might use the appropriate SQL calls to return the Database name (do you mean SQL Server? Or are you saying a database which supports SQL? SQL is a programming language, not a database).
If it's a table in the Access database, then you should be able to use a Query in the Access database as your row source and include the table name within it.
Are you in too much of a hurry to clarify or are you new to databases and tables? If the latter, my apologies for this, but you seem to be rather lazy and/or hurried in your posts.
-k
How does Crystal know which table to use each time?
If you're changing it manually in the report, you might manually change it in the report.
Do you want the database name or the table name, the first post speaks of databases, the second of tables.
If the database, than it sounds like it's always an Access database so you can use a hardcoded value. If you mean the name of the linked SQL Server database containing a table, you might use the appropriate SQL calls to return the Database name (do you mean SQL Server? Or are you saying a database which supports SQL? SQL is a programming language, not a database).
If it's a table in the Access database, then you should be able to use a Query in the Access database as your row source and include the table name within it.
Are you in too much of a hurry to clarify or are you new to databases and tables? If the latter, my apologies for this, but you seem to be rather lazy and/or hurried in your posts.
-k
- Thread starter
- #5
New and a bit hurried - the person responsible for this quit and left no documentation and this is way out of my realm of knowledge so I don't know what all I need to say to get good answers and yes I meant tables not the actual database that's my bad.
synapsevampire
Programmer
I had asked how Crystal knows which table to use, and you didn't bother to answer that either.
I give.
-k
I give.
-k
- Thread starter
- #7
There's a function on the Database menu that is called "Set Location".
Everytime a new table is created in the Access database, "Set Location" is used to point crystal at the table that was just created. All the other tables (on the SQL server) are never changed - just the Access one.
That is a picture of the actual "Set Location" menu. This feature allows you to change physical location and names of tables within Crystal. The date on the Error Report database is what changes - the Error Report database being the one that is created in Access.
If there is not a simple function that would return usuable tables for Crystal Reports 8 then that's fine I will create an additional field in the Access tables that contains the date but if there is a way to do it I would much rather go that route than do something by hand.
Everytime a new table is created in the Access database, "Set Location" is used to point crystal at the table that was just created. All the other tables (on the SQL server) are never changed - just the Access one.
That is a picture of the actual "Set Location" menu. This feature allows you to change physical location and names of tables within Crystal. The date on the Error Report database is what changes - the Error Report database being the one that is created in Access.
If there is not a simple function that would return usuable tables for Crystal Reports 8 then that's fine I will create an additional field in the Access tables that contains the date but if there is a way to do it I would much rather go that route than do something by hand.
synapsevampire
Programmer
Sounds like the difficult way to accomplish this all the way around.
Instead of using a different Access table each month, reopening the report each time and resetting the data source, create a Query in Access which pulls from the table, and simply modify the query each month to pull from the appropriate table.
You can even use VBA code to derive the latest table name.
Stating that you manually change it would have sufficed, but I do appreciate your effort to supply meaningful information.
-k
Instead of using a different Access table each month, reopening the report each time and resetting the data source, create a Query in Access which pulls from the table, and simply modify the query each month to pull from the appropriate table.
You can even use VBA code to derive the latest table name.
Stating that you manually change it would have sufficed, but I do appreciate your effort to supply meaningful information.
-k
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question