Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Record Selection problem

Status
Not open for further replies.

nnaacc

Technical User
Mar 1, 2000
18
US
I'm having a heck of a time with nested record selection formulas.

I've got on 3 linked tables - Buyers =* Orders *= Sellers
I need to display all orders which had XYZ as a buyer or seller, and only info from the -1 version of the buyer or seller table. The problem I'm having is: how to select the -1 version without excluding orders which only have a buyer or seller (but not both). Here is my formula:

({Orders.BuyerName}= "XYZ" or {Orders.SellerName}= "XYZ") and
({Buyers.version}= -1 or IsNull({Buyters.Version}) and
({Sellers.version}= -1 or IsNull({Sellers.Version})

I know the tables are linked together correctly because if I eliminate the last two rows, it returns all possible combinations (inlcuding only one buyer or seller)

Thanks in advance for any help.
 
You were missing a couple of right parentheses, and you should check for nulls before you check the field value. If this still doesn't work, include your error message in the post.
(
{Orders.BuyerName}= "XYZ" or
{Orders.SellerName}= "XYZ"
) and
(
IsNull({Buyters.Version}) or
{Buyers.version}= -1
) and
(
IsNull({Sellers.Version}) or
{Sellers.version}= -1
) Malcolm Wynden
I'm for sale at malcolm@wynden.net
 
Malcolm W,

Thank you, thank you, thank you !!!
Either the correct braceting, or the putting IsNull first made it work. FYI - I didn't make too clear before - the query never gave error messages, it just would either exclude all orders without a buyer AND seller, or include all versions of Buyer / Seller linked to the order table.

But now, it's great :)

NAC
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top