CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Mar 2008
    Posts
    36

    Question joins with reports

    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

  2. #2
    Join Date
    Dec 2006
    Location
    Pune, India.
    Posts
    579

    Re: joins with reports

    Whatever you have written seems correct.

    I think you have to use '#' for filtering date column in access.

  3. #3
    Join Date
    Mar 2008
    Posts
    36

    Question Re: joins with reports

    Thanks for response
    My problem is not in date but it is that
    it is not filtering scope values
    so please help me
    thanks
    Regards
    shruti

  4. #4
    Join Date
    Dec 2006
    Location
    Pune, India.
    Posts
    579

    Re: joins with reports

    I didn't get this in your query.

    Code:
    s.Scope=p.Scope=" & "'" & frmPurchaseReport.cmbScope.Text & "'

  5. #5
    Join Date
    Mar 2008
    Posts
    36

    Question Re: joins with reports

    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

  6. #6
    Join Date
    Dec 2006
    Location
    Pune, India.
    Posts
    579

    Re: joins with reports

    Shruti,

    Your query should be like this.
    Code:
    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 " & _
            "AND P.Scope = '" & frmPurchaseReport.cmbScope.Text & "' " & _
            "AND P.Date = #" & frmPurchaseReport.dtpRDate.Value & "#", cn, adOpenDynamic, adLockOptimistic, adCmdText

    It is not related to crystal report at all.

  7. #7
    Join Date
    Mar 2008
    Posts
    36

    Question Re: joins with reports

    hello Sir,
    thanks for code.
    but by this query it displays all records in productitem,
    scope name is not reflecting.

    i want to filter records from productitem by date and scope.

    pls help me
    what will be the query?

    regards,
    shruti

  8. #8
    Join Date
    Dec 2006
    Location
    Pune, India.
    Posts
    579

    Re: joins with reports

    I have filtered data for ProductItem.

    Look here:

    Code:
    "AND P.Scope = '" & frmPurchaseReport.cmbScope.Text & "' " & _
    "AND P.Date = #" & frmPurchaseReport.dtpRDate.Value & "#"

    Check whether your data is correct!

  9. #9
    Join Date
    Mar 2008
    Posts
    36

    Question Re: joins with reports

    hi ,
    Sorry sir,
    it is not working....
    it gives all records from productitem irrrespective of date.

    pls help me
    regards,
    shruti

  10. #10
    Join Date
    Dec 2006
    Location
    Pune, India.
    Posts
    579

    Re: joins with reports

    Can you attach your database here? (of course if possible)


    or list few records from both records which you expect to come in output.

  11. #11
    Join Date
    May 2008
    Posts
    224

    Re: joins with reports

    Code:
     
    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 inner join ProdcutItem p " & _
    		"on s.scope=p.scope " &_
    		"WHERE P.Scope = '" & frmPurchaseReport.cmbScope.Text & "' " & _
    		"AND P.[Date] = #" & frmPurchaseReport.dtpRDate.Value & "#", cn, adOpenDynamic, adLockOptimistic, adCmdText
    Last edited by WillAtwell; July 10th, 2008 at 01:59 PM.

  12. #12
    Join Date
    May 2008
    Posts
    224

    Re: joins with reports

    One other thing I noticed above there are brackets around date in the first part but not in the second. This could be causing a problem.

    As a general rule I never use reserved words like date for field names.

  13. #13
    Join Date
    Dec 2006
    Location
    Pune, India.
    Posts
    579

    Re: joins with reports

    Quote Originally Posted by WillAtwell
    Code:
     
    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 inner join ProdcutItem p " & _
    		"on s.scope=p.scope " &_
    		"WHERE P.Scope = '" & frmPurchaseReport.cmbScope.Text & "' " & _
    		"AND P.[Date] = #" & frmPurchaseReport.dtpRDate.Value & "#", cn, adOpenDynamic, adLockOptimistic, adCmdText

    Will using INNER JOIN syntax solve the problem?

    Is there any difference between query written by me and yours?

  14. #14
    Join Date
    May 2008
    Posts
    224

    Re: joins with reports

    Quote Originally Posted by Shaikh.Riyaz.a
    Will using INNER JOIN syntax solve the problem?

    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.

  15. #15
    Join Date
    Mar 2008
    Posts
    36

    Question Re: joins with reports

    Hello Sirs,

    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
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by shruti23; July 11th, 2008 at 04:15 AM.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured