Hello Sirs,
i m using vb6,ms access 2000,cr9.
my problem is i want display data in crystal report by using joins because data is cooleted from two tables.
my code is as
Report.DiscardSavedData
Set rs = New ADODB.Recordset
rs.Open "Select s.Scope,s.RF,s.RevisionNo,p.Date,p.Scope,p.ItemName,p.ItemCode,p.QtyInStock,p.QtyOrdered,p.Remark from ScopeMaster s ,ProductItem p Where s.Scope=p.Scope=" & "'" & frmPurchaseReport.cmbScope.Text & "' And p.Date=" & frmPurchaseReport.dtpRDate.Value, cn, adOpenDynamic, adLockOptimistic, adCmdText
Report.Database.SetDataSource rs
For i = 1 To Report.Database.Tables.Count
Report.Database.Tables(i).SetDataSource (App.Path & "\DB\WinStock.mdb")
Report.Database.Tables(i).Location = App.Path & "\DB\WinStock.mdb"
Report.Database.Tables(i).SetLogOnInfo "", App.Path & "\DB\WinStock.mdb", "Admin", "becool"
Next i
CRViewer91.ReportSource = Report
CRViewer91.Refresh
CRViewer91.ViewReport
it dispaly blank report even database contains records.
Pls help me urgently.........................
Regards
jggtz
July 5th, 2008, 02:31 AM
rs.Open "Select s.Scope,s.RF,s.RevisionNo,p.Date,p.Scope,p.ItemName,p.ItemCode,p.QtyInStock,p.QtyOrdered,p.Remark from ScopeMaster s ,ProductItem p Where s.Scope=p.Scope=" & "'" & frmPurchaseReport.cmbScope.Text & "' And p.Date=" & frmPurchaseReport.dtpRDate.Value, cn, adOpenDynamic, adLockOptimistic, adCmdText
Is it ok?
Where s.Scope=p.Scope=
shruti23
July 10th, 2008, 01:17 AM
hello sir,
pls tell me how do i wrote join query in cr9.
pls tell me i m stucking here.............
there are two tables with scope field common in both tables.
so pls tell me what is procedure or what is syntax of that query.
also i have to view records with scope field is selected at runtime and related date field.
date is not common in both
only scope id common.
so pls tell me.....
regards
shruti
WillAtwell
July 11th, 2008, 02:50 AM
2 things.
1: Have you tried referencing the date field as [date]?
2: Does the date field contain time as well?
I have saw many queries fail on a date= filter because because of the time portion. for example 7/11/2008 != 7/11/2008 3:30 AM
I have also saw many queries fail because of a reserved word as a field name without [ ] on it.
WillAtwell
July 11th, 2008, 02:53 AM
btw I normally use the join statement to join tables.
You also do not need to select the scope from both tables though I did leave it in here.
shruti23
July 11th, 2008, 07:11 AM
hello Sir,
pls tell me syntax of report.SQLQueryString
i m getting error here for code
Report.SQLQueryString = "SELECT S.Scope, S.RF, S.RevisionNo, P.[Date], P.Scope, P.ItemName, P.ItemCode, P.QtyInStock, P.QtyOrdered,P.Remark FROM ScopeMaster S inner join ProductItem P on S.Scope=P.Scope Where P.Scope= '" & frmPurchaseReport.cmbScope.Text & "' AND P.Date = #" & frmPurchaseReport.dtpRDate.Value & "#" ' , cn, adOpenDynamic, adLockOptimistic, adCmdText"
Report.Database.SetDataSource rs
Report.Database.AddADOCommand cn, rs.ActiveCommand
pls tell me
regards
shruti
WillAtwell
July 11th, 2008, 12:05 PM
hello Sir,
pls tell me syntax of report.SQLQueryString
i m getting error here for code
Report.SQLQueryString = "SELECT S.Scope, S.RF, S.RevisionNo, P.[Date], P.Scope, P.ItemName, P.ItemCode, P.QtyInStock, P.QtyOrdered,P.Remark FROM ScopeMaster S inner join ProductItem P on S.Scope=P.Scope Where P.Scope= '" & frmPurchaseReport.cmbScope.Text & "' AND P.Date = #" & frmPurchaseReport.dtpRDate.Value & "#" ' , cn, adOpenDynamic, adLockOptimistic, adCmdText"
Report.Database.SetDataSource rs
Report.Database.AddADOCommand cn, rs.ActiveCommand
pls tell me
regards
shruti
Is db supposed to be commented out of the connect string?
You also left out the brackets around date in the where clause.
Beyond that I would have to know what the error is you are getting and on which line it occurs.
shruti23
July 16th, 2008, 07:34 AM
Hello Sir,
i m attaching database password is becool.
if i have given below code then it gives error as not supported
DetailsAO error code:0xbf5
SourceAO.Database
Description:too few parameters, expected 6
Report.SQLQueryString = "SELECT S.Scope, S.RF, S.RevisionNo, P.[Date], P.Scope, P.ItemName, P.ItemCode, P.QtyInStock, P.QtyOrdered,P.Remark FROM ScopeMaster S, ProductItem P WHERE S.Scope=P.Scope AND P.Scope= '" & frmPurchaseReport.cmbScope.Text & "' AND P.Date = #" & frmPurchaseReport.dtpRDate.Value & "#"
pls help me.
in previous post using rs.open there is no error but it is not reflecting desired results.it display all records from table.so pls help me..
i m stucking here.......
thanks
Regards,
shruti
shruti23
July 18th, 2008, 01:57 AM
Respected K babu Sir,
pls tell me solution for my problem.
i m stuckingggg here.................
regards
shruti
jggtz
July 18th, 2008, 01:53 PM
Attach your RPT file, please
shruti23
July 19th, 2008, 04:56 AM
hello Sir,
Thanks for resonse.
i m attaching rpt file it is in a purchasereport.pdf file.
it is not showing scope name and not filtering values with respective date and scope.
pls tell me solution.
i m stucking..............here
thanks
regards,
shruti
jggtz
July 19th, 2008, 10:04 PM
I men the .RPT file itself
The file created by Crystal reports
I need to see the data source properties in order to try to help you
shruti23
July 20th, 2008, 12:44 AM
hello Sir,
thanks for response.
sorry for wrong file.
now i m sending u correct rpt file.
so pls help me.
its urgent..
regards,
shruti
jggtz
July 20th, 2008, 06:18 PM
Try
The relevant part is that you have to hard write the tables' name and convert to text the date (I'm using dd/mm/yyyy, perhaps you need mm/dd/yyyy)