• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 362
  • Last Modified:

MS Access - Check Box to show updated records in List Box

Hi -
I have three tables with the column Updated_Date

Company.Updated_Date
Contacts.Updated_Date
Activity.Updated_Date

What I would like to happen is:
If the Updated_Date in any of the above tables is greater than 1/1/2009,
then I would like the Companies to show in my ListBox (Control Name: List16)

My form name is CompanyMain

Currently the rowsource for List16 is:
    Me.List16.RowSource = "SELECT Company.Company_Name, Company.DUNS_Number FROM Company GROUP BY Company_Name, DUNS_Number;"

Note: Company.DUNS_Number is Unique and shown in all tables.

If the Box is UnChecked the ListBox would revert to it's orig list.
Or I have a Reset Command Button I could add a line to to clear the criteria...
Thanks for your help.
Stacy

0
stacydr
Asked:
stacydr
  • 2
  • 2
1 Solution
 
brandonvmooreCommented:
Are you saying that these 3 tables are related by the DUNS_Number field?
And are you just needing help with the construction of the SELECT statement, or do you need help with more than that?

I'll see if I can post an answer tomorrow evening if someone else doesn't get to it first.
0
 
brandonvmooreCommented:
Sunday evening, that is
0
 
peter57rCommented:
To get the rowsource for your listbox you will have to find all the DUNS numbers from all the tables that have an updated date >1/1/2009.

You do this in a Union query.

Select Duns_Number from Company where Updated_Date >=#2009/01/01#
Union
Select Duns_Number from Contacts where Updated_Date >=#2009/01/01#
Union
Select Duns_Number from Activity where Updated_Date >=#2009/01/01#

Save this as qry1

Change the rowsource to:
"SELECT Company.Company_Name, Company.DUNS_Number FROM Company
Inner Join qry1 on company.DUNS_Number = qry1.DUNS_Number
 GROUP BY Company_Name, Company.DUNS_Number;"



0
 
stacydrAuthor Commented:
Hi Brandon.. thank you for your post, I wasn't quite sure what I needed, but Peter..'s ideas worked well.
Thanks again.
Stacy
0
 
stacydrAuthor Commented:
Hi peter... thank you for your solution, I wasn't sure what kind of formula or function I needed and this worked well.  Thanks again..
Stacy
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now