Relating to a field on a subform

[JeffGMACI]

Hello everyone - I have a question and wanted to see if anyone had any ideas for me. I have a table with customer data - customer number, address, etc. Then, I have three tables for different products ordered:

tblLocation tblTV
CusNumber (PK) AutoNum (PK)
Address CusNumber (FK)
OrderDate

tblRadio and tblNewspaper have the exact structure of tblTV.

My question to you is this - to make my life easier when I go to filter and query things, I would like to add a yes/no box to the Location table. This box would only be checked if the OrderDate field was not null on any of the three tables. This way, I could do a quick query by customer number and see if they have ordered anything. I'm just not sure how to releate the check box to the three fields in my other tables. Do you guys have any ideas on how I can do this?

I tried using something like this on a form in the control source for the check box:
=Not IsNull([tblTV.OrderDate])
but that didn't work.

Any ideas? Thanks in advance,
Jeff

 

[pdldavis]

Hi, another way to approach that would be to build a combo box on your form with the record source as your customer number.
Build a query that uses the combox box selection as criteria for the customer number query. Bring in your other tables and use criteria 'is not null' for the date fields. You'll need to stagger them like this:

CustID TVDate NewsDate OtherDate
[Forms]![frm1]!CboCust Is Not Null
[Forms]![frm1]!CboCust Is Not Null
[Forms]![frm1]!CboCust Is Not Null

Something like that might work for you.