470,647 Members | 1,204 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,647 developers. It's quick & easy.

Parenthesis doesn't work for referencing query fields

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.


Nov 12 '05 #1
1 1728
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.

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by loRdK | last post: by
10 posts views Thread by Mr. x | last post: by
5 posts views Thread by Shimon Sim | last post: by
1 post views Thread by Korara | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.