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
hello sir,
Actually i have to tables
one is Scopemaster with scope,rf,revisionno
second is roductItem with scope, item code, item name ,date,purchaseinformation.
So i want data from scopemaster and from productitem table also where scope is common.that scope is selected from combo of frmpurchasereport form.
so that i have given that.
pls tell me how do i write join query in CR9?
thankx in advance
regrads
shruti
Is there any difference between query written by me and yours?
I pretty much always use the join syntax, will that solve the problem here? No the other query should return a joined table as well.
The date field is likely the problem. Without knowing what the value of the vars used are as well as the data in the fields targeted for filtering I can't say for sure. I do know that referencing field names like date without using the [] can often lead to errors or unexpected results.
In your query you used p.[date] in the select portion but p.date in the where portion. I assume this was just a typo. With the addition of the brackets I would expect it to work as well assuming that the vars contain valid filter data.
thanks for response.
I m attaching report pls check.
it will display all records irrespective of date also scope name is not displaying.
pls tell me solution.
your inner join query is not working.
also 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.
i m stucking here.
thanks
Regards,
shruti
Last edited by shruti23; July 11th, 2008 at 04:15 AM.
* The Best Reasons to Target Windows 8
Learn some of the best reasons why you should seriously consider bringing your Android mobile development expertise to bear on the Windows 8 platform.