Solved

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

Posted on 2009-04-11
5
353 Views
Last Modified: 2013-11-28
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
Comment
Question by:stacydr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 5

Expert Comment

by:brandonvmoore
ID: 24124828
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
 
LVL 5

Expert Comment

by:brandonvmoore
ID: 24124829
Sunday evening, that is
0
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 24124853
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
 
LVL 2

Author Comment

by:stacydr
ID: 24127948
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
 
LVL 2

Author Closing Comment

by:stacydr
ID: 31569278
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question