Parenthesis doesn't work for referencing query fields

[sinthreck]

have a query which gets data from two tables.
There's about 20 fields worth of data.

In VB code, I run this query:

strPOItems = "SELECT * FROM [Order Details] LEFT JOIN
OutstandingInvoices ON ([Order Details].PartNumber =
OutstandingInvoices.PartN) AND ([Order Details].OrderNo =
OutstandingInvoices.PONumber) AND ([Order Details].ItemNo =
OutstandingInvoices.ItemNo) AND ([Order Details].SubOrder =
OutstandingInvoices.POSuborder) AND ([Order Details].TypeID =
OutstandingInvoices.TypeID) WHERE ((([Order Details].OrderNo)= " &
ready!OrderNo & ") AND (([Order Details].SubOrder)=""A"") AND
((OutstandingInvoices.ItemNo) Is Null) AND
((OutstandingInvoices.POSuborder) Is Null) AND
((OutstandingInvoices.PONumber) Is Null) AND
((OutstandingInvoices.PartN) Is Null) AND
((OutstandingInvoices.TypeID) Is Null));"
Set POItemsQuery = m.CreateQueryDef("", strPOItems)
Set POItems = POItemsQuery.OpenRecordset
When I want to store the data into another table I'm having problems
referencing the data.

For example:

Invoice!TypeID = POItems!TypeID

Gives me a 3265 runtime error.

So when I changed the line to this:

Invoice!TypeID = POItems![Order Details].TypeID

It gives me the error "Method or data member not found"
I would like to know what the correct syntax is to address the query
result.
Thankyou.

[Salad]

sinthreck wrote:

have a query which gets data from two tables.
There's about 20 fields worth of data.

In VB code, I run this query:

strPOItems = "SELECT * FROM [Order Details] LEFT JOIN
OutstandingInvoices ON ([Order Details].PartNumber =
OutstandingInvoices.PartN) AND ([Order Details].OrderNo =
OutstandingInvoices.PONumber) AND ([Order Details].ItemNo =
OutstandingInvoices.ItemNo) AND ([Order Details].SubOrder =
OutstandingInvoices.POSuborder) AND ([Order Details].TypeID =
OutstandingInvoices.TypeID) WHERE ((([Order Details].OrderNo)= " &
ready!OrderNo & ") AND (([Order Details].SubOrder)=""A"") AND
((OutstandingInvoices.ItemNo) Is Null) AND
((OutstandingInvoices.POSuborder) Is Null) AND
((OutstandingInvoices.PONumber) Is Null) AND
((OutstandingInvoices.PartN) Is Null) AND
((OutstandingInvoices.TypeID) Is Null));"
Set POItemsQuery = m.CreateQueryDef("", strPOItems)
Set POItems = POItemsQuery.OpenRecordset
When I want to store the data into another table I'm having problems
referencing the data.

For example:

Invoice!TypeID = POItems!TypeID

Gives me a 3265 runtime error.

So when I changed the line to this:

Invoice!TypeID = POItems![Order Details].TypeID

It gives me the error "Method or data member not found"
I would like to know what the correct syntax is to address the query
result.
Thankyou.

Near as I can determine TypeID is contained in OutstandingInvoices and
is not included as a field column in the query. Could that be your
problem? That you forgot to add it to the query? If you did forget,
that can be fixed by adding that field to the SQL statement.