Results 1 to 3 of 3

Thread: [RESOLVED] need help with sql statement

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Dec 2010
    Posts
    105

    Resolved [RESOLVED] need help with sql statement

    hi guys,

    im trying to create a datareport in vb6 from an access db. im trying to get data from two tables. Im struggling to add the values of the two fields from the two tables and create a temporary field in the query so i can use it on the datareport.

    tables: table1, table2.
    Field names field(0), and field(0) (i have brackets in my field names and field names are the same in both tables)

    I tried this:
    Code:
    Private Sub Command14_Click()
    'Create a RS
    Dim r As Recordset
    Set r = New ADODB.Recordset
    With r
        .ActiveConnection = c
        .CursorType = adOpenDynamic
        .LockType = adLockOptimistic
        .source = "SELECT*,table1.field(0), table2.field(0), val(table1.field(0))+val(table2.field(0)) as total1 from table1,table2 Where orderno = " & " '" & cmbOrderno.Text & "'"
        .Open
    'Bind to the report
    'If IsNull(r("custname")) Then
     '   MsgBox "Enter Customer Name and Customer Vat No first in Order Transaction on this page before generating invoice"
      '  Else
      
        
    Set DRtest.DataSource = r
    
    'Preview the report
    DRtest.ExportReport rptKeyHTML, "C:\Program Files\LinYehfinalv8\forms\invoices\Order" & frmMasteredit.cmbOrderno.Text & "invoice", True
    DRtest.Show vbModal
    
    r.Close
    Set r = Nothing
    'End If
    End With
    End Sub
    i keep getting an undefined function "table1.field1" in expression, i have a feeling its because of the brackets?

    Also i tried to create a simple query in access to display only these two fields in my query, can i refer to the access query instead of referring to the two tables?

    any help would be greatly appreciated.
    Cheers

  2. #2
    Fanatic Member amrita's Avatar
    Join Date
    Jan 2007
    Location
    Orissa,India
    Posts
    888

    Re: need help with sql statement

    Try writing table1.[field(0)] and check
    thanks
    amrita

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Dec 2010
    Posts
    105

    Re: need help with sql statement

    hey Amrita, i tried thanks, i enclosed the whole field with [] and it worked. Thanks

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