Results 1 to 3 of 3
  1. #1
    JackA is offline Novice
    Windows XP Access 2000
    Join Date
    Jul 2011
    Posts
    10

    Filtering Reports

    I am trying to produce a report but struggling.

    My database is essentially as follows.

    Note: Tables are linked to Excel spreadsheets and can not be editted.

    Table 1 = Code, Assets Amount, Assets Date

    e.g.

    ABC 100 01/02/2003
    ABC 800 01/03/2003
    ADB 900 01/02/2003
    ADB 750 01/03/2003

    Table 2 = Code, Sales, Sales Date

    e.g.

    ABC 2 01/01/2003 to 01/02/2003
    ABC 3 01/02/2003 to 01/03/2003
    ADB 4 01/01/2003 to 01/02/2003
    ADB 8 01/02/2003 to 01/03/2003

    Query = Code, Assets, Assets Date, Sales, Sales Date

    e.g.

    ABC 100 01/02/2003 2 01/01/2003 to 01/02/2003
    ABC 800 01/03/2003 2 01/01/2003 to 01/02/2003
    ABC 100 01/02/2003 3 01/02/2003 to 01/03/2003
    ABC 800 01/03/2003 3 01/02/2003 to 01/03/2003
    ADB 900 01/02/2003 4 01/01/2003 to 01/02/2003
    ADB 750 01/03/2003 4 01/01/2003 to 01/02/2003
    ADB 900 01/02/2003 8 01/02/2003 to 01/03/2003
    ADB 750 01/03/2003 8 01/02/2003 to 01/03/2003



    I now want to make a report that filters the Asset Date and Sales Date so the following data is generated.

    ABC 100 01/02/2003 2 01/01/2003 to 01/02/2003
    ABC 800 01/03/2003 3 01/02/2003 to 01/03/2003
    ADB 900 01/02/2003 4 01/01/2003 to 01/02/2003
    ADB 750 01/03/2003 8 01/02/2003 to 01/03/2003

    Basically as you can see, I want the Asset Date to match the end of the Sales Date.

    Is this possible?

    Can I do this when generating a report? Or do I need to do this earlier, at query or table stage?

    Thanks!

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    This is a really bad data structure but you don't really have a choice so as long as the SALESDATE field ALWAYS, ALWAYS, ALWAYS has the same format (10 character dates)

    You could add an additional field to your query that says

    Match: iif([AssetsDate] = right([SALESDATE], 10), "yes", "no")

    This will add a field to the end of your query that shows whether or not the asset date (assuming you have this linked as a text field also) matches the last 10 characters of the SALESDATE field.

    note also I removed any spaces from field names, it's really not a good idea to have any spaces or special characters in field names, it really has a tendency to louse things up when you're programming.

  3. #3
    JackA is offline Novice
    Windows XP Access 2000
    Join Date
    Jul 2011
    Posts
    10
    That's great! Thanks for your help!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Filtering
    By BannedOak in forum Access
    Replies: 9
    Last Post: 05-22-2011, 02:10 PM
  2. Reports w/ sub-reports very slow to open
    By vaikz in forum Reports
    Replies: 2
    Last Post: 02-27-2011, 08:41 AM
  3. Access Reports drop sub-reports
    By Kevin Ellis in forum Reports
    Replies: 0
    Last Post: 11-19-2010, 03:28 PM
  4. filtering
    By nashr1928 in forum Forms
    Replies: 12
    Last Post: 07-01-2010, 06:30 AM
  5. Filtering my report
    By ldarley in forum Reports
    Replies: 1
    Last Post: 09-05-2008, 09:14 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums