-
Mar 7th, 2011, 05:47 AM
#1
Thread Starter
Lively Member
[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
-
Mar 7th, 2011, 08:09 AM
#2
Re: need help with sql statement
Try writing table1.[field(0)] and check
-
Mar 7th, 2011, 08:22 AM
#3
Thread Starter
Lively Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|