By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,367 Members | 1,279 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,367 IT Pros & Developers. It's quick & easy.

Parenthesis doesn't work for referencing query fields

P: n/a
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
Share this Question
Share on Google+
1 Reply

P: n/a
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.