Crystal Report X & Access 2003 Db

I am running CR X and Access 2003 database.

I have a table defined as Employee and one of the fields is called RESDate and is defined as Date/Time.

The RESDate has a default value Date () - 1. Bascially, I am deriving yesterday's date. When I view the table in Access, I see yesterday's date in the field.

Now in CR X:
I made a new connection and brought in this Access database and this one table. I moved the RESDate to detail line.

I do not see yesterday's date in CR. I did browse of the field in CR X and it shows blanks also. Any ideas ?

Thanks for your help in advance.

Regards
LB1That sounds odd, in CR right click on the field name and select show type,
does the field show up as a string ? or as date / time or ? Also can you create a query, or a view and try reporting off the query / view ?

Crystal 8.5 Summing question

In a one to many join How do you sum a field coming from the one table. example:
Table 1 has the following columns:
Employee id
check number
Gross pay
Table 2 has
Employee id
check number
ded code
ded amount
The data is as follows

employee id check number Gross Pay
1 1 100
1 2 100
1 3 100

employee id check number ded code ded amt
1 1 A 10
1 1 B 5
1 1 C 1

Each check would have the same 3 deductions.
When these tables are joined by employee id and check number you get a total of 9 records. If you wanted the total gross for the employee you can not just sum gross pay because you would get 900. The real gross for the employee should be 300.
I have been able to create a formula that gets the gross of 100 on one of the record and 0 on the other two records for each check, but Crystal will not let me sum that field.
Any help would be good.Try to create a Group on Employee ID so that it's not repeated. The records under an employee id will be displayed in Detail Section.

Create a summary field on gross pay and place it in Group Footer. this will returns the gross pay without duplication. To sum the check amout, create a separate sum field/running total field.

Hope this work.

Cross Database Issue

Hi

I have a view which is like following

Like

DB1.View1

Select * from Accounting,Employee

where Accounting.Emp_Id = Accounting.Emp_Id

and i am using this view in DB2.storedProcedure1, but this gives me an error when I execute this Stored procedure from VBA code. the same SP works from query analyser with same user_id and pwd

Error - Could not find database id 9. Database may not be activated yet or may be in transition.

If I change the view to following the SP works

DB1.View1

Select * from DB1.Accounting,DB1.Employee

where DB1.Accounting.Emp_Id = DB1.Accounting.Emp_Id

But I dont want to change the view as i dont own it.

Please suggest the solution for this.

Thanks in Advance.

Ashutosh

Moving your question to a different thread, where they can offer more help.

Thanks

Richard Cook

VS Debugger Team

|||

Is there a synonym defined on the objects mentioned, forcing SQL Server to go to a non-existing database ? Did you trace the command which is fired against the database using SQL Profiler ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

You did not mentioned the version and the service pack of sql server. IF its 2000 check this, it seems to be u have not applied the patches

http://support.microsoft.com/kb/834688

Madhu

|||

Thanks Jens,

the issue is - I have a view in one database which i refer from another database.

e.g. DB1 has view V1 and i need to use it in a SP1 in DB2

so i refer to the view as DB1.V1 in DB2.SP1. but the tables inside DB1.V1 are without database reference i.e. without [db].[tables]..its just- "select a from [table]"

and i run this DB2.SP1 from sql server query analyzer with login "abc",pwd - "abc". This works perfectly fine.

but when i run the same SP from my VBA code with same login "abc" pwd "abc"..it gives me above specified error.

but..if i fix the view..to "select a from [db].[dbo].[table]...it works from my VBA code as well.

therefore it makes me think that i need to change the view to set all the tables as [db].[table]...but as the db name changes from environment to environment i.e. development/integration...we can not put [db].[table]..

INSTEAD i need something like [currentDB].[Table].

Is that possible in sql server 2000

|||

Thanks Madhu.

I did saw that post on support.microsoft.com but here there are 2 things

1. It was working till yesterday and stoped working today..so can it be an issue with user privilages?

2. Its working with the solution that i have implmeneted, but that is too specific and i need generic solution.

Its SQL server 2000.

please see this

the issue is - I have a view in one database which i refer from another database.

e.g. DB1 has view V1 and i need to use it in a SP1 in DB2

so i refer to the view as DB1.V1 in DB2.SP1. but the tables inside DB1.V1 are without database reference i.e. without [db].[tables]..its just- "select a from [table]"

and i run this DB2.SP1 from sql server query analyzer with login "abc",pwd - "abc". This works perfectly fine.

but when i run the same SP from my VBA code with same login "abc" pwd "abc"..it gives me above specified error.

but..if i fix the view..to "select a from [db].[dbo].[table]...it works from my VBA code as well.

therefore it makes me think that i need to change the view to set all the tables as [db].[table]...but as the db name changes from environment to environment i.e. development/integration...we can not put [db].[table]..

INSTEAD i need something like [currentDB].[Table].

Is that possible in sql server 2000