clauses and punctuation

[netscamp]

Ok, I've got the code below working, but I need to add some data from other tables. The select statement can have multiple from clauses?
I can't find it in ACCESS help, and the book I ordered from Amazon is in the mail !


Private Sub Command13_Click()

Dim dbs As DAO.Database
Dim qdf As DAO.Querydef
Dim strSQL As String
Dim lkval As String
Dim jones As String
Dim james As String
james = Format(Now, "mdyyhss&quot
Set dbs = CurrentDb
lkval = PO_number.Value
jones = "po#_query" & "_" & lkval & "_" & james
----------------
strSQL = "SELECT * FROM po_table WHERE po_num = " & Chr(34) & lkval & Chr(34)

Set qdf = dbs.CreateQueryDef(jones, strSQL)

End Sub

 

[JunglesMcBeef]

If you want data from more than one table use a comma and add it in... eg SELECT * FROM po_table, a_table WHERE ...etc

 

[netscamp]

What if you want to pick specific fields from specific tables ?

Ireally appreciate your time, and I'm going to contibute to the support of this service....


Thanks,
RjW

 

[netscamp]

I'm getting a missing operator error from ACCESS when I run this. It's in the select statement.

Private Sub Command13_Click()
Dim dbs As DAO.Database
Dim qdf As DAO.Querydef
Dim strSQL As String
Dim lkval As String
Dim jones As String
Dim james As String
james = Format(Now, "mdyyhss&quot
Set dbs = CurrentDb
lkval = poPoNumTxt.Value
jones = "po#_query" & "_" & lkval & "_" & james
strSQL = "SELECT * FROM po_table WHERE poPonum = " & Chr(34) & lkval & Chr(34) & " transPatient, transDoctor FROM trans_table WHERE transPoNum = " & Chr(34) & lkval & Chr(34)
Set qdf = dbs.CreateQueryDef(jones, strSQL)
End Sub

 

[JunglesMcBeef]

I'm not too sure on this but I think first try this line:

strSQL = "SELECT po_table.*, trans_table.transPatient, trans_table.transDoctor FROM po_table, trans_table WHERE poPonum = " & Chr(34) & lkval & Chr(34) & "OR transPoNum = " & Chr(34) & lkval & Chr(34)"

If this doesn't work try entering all the po_table fields in the SELECT clause (make sure to have po_table attached to each one). Good luck

 

[netscamp]

Ok, first, I would like to say, I've just spent $90 on an Access VBA book, and an SQL book! and I've been poring over them, and referring to them like a demon with a fever..., but I can't get this right. It's working somewhat, but I know the query should return 5 items, but it is returning 25. It's multiplying them, is that a cartesian product...? So some sort of join ? Union ?
Bless you all, genius saints....

Dim dbs As DAO.Database
Dim qdf As DAO.Querydef
Dim strSQL As String
Dim lkval As String
Dim jones As String
Dim james As String

james = Format(Now, "mdyyhms&quot
Set dbs = CurrentDb
lkval = transPoNumTxt.Value
jones = "po#_query" & "_" & lkval & "_" & james

strSQL = "SELECT trans_table.transPoNum, trans_table.transPatient, trans_table.transDoctor, po_table.PoDate"
strSQL = strSQL & " FROM trans_table, po_table"
strSQL = strSQL & " WHERE transPoNum = " & Chr(34) & lkval & Chr(34)
strSQL = strSQL & " AND poPoNum = transPoNum"

Set qdf = dbs.CreateQueryDef(jones, strSQL)
End Sub

 

[JunglesMcBeef]

Is the query returning 5 items of each item? Have you checked it isn't an underlying problem with the database? (I spent a whole day sweating over a simple program problem and ended up working out it was a data type in my tables that was causing the problem). Also check how many times the querydef is running.