1

I have a form in Access 2010 that's used as a search form to filter records matching specific criteria.

I transferred information in the backend from one set of tables to another. Now, the filter doesn't work. Even if I leave all the criteria blank - ie. set it to bring up all records - it tells me, 'No records found.'

I've remapped the tables a few times, made sure they all have information, and are linking and opening properly. What could be preventing Access from finding the records?

Here's the filter query, if it helps any. It doesn't appear to be filtering properly, even though it works fine with the old tables.

    SELECT Activity.*, ActivityCash.*, EngSchDates.*, Monitoring.*, Procurement.*, 
    LookupDistrict.*
        FROM ((((Activity LEFT JOIN LookupDistrict ON Activity.District = 
LookupDistrict.District) INNER JOIN ActivityCash ON Activity.GWP = ActivityCash.GWP) 
INNER JOIN EngSchDates ON Activity.GWP = EngSchDates.GWP) 
INNER JOIN Procurement ON Activity.GWP = Procurement.GWP) INNER JOIN Monitoring ON 
Activity.GWP = Monitoring.GWP ORDER BY Activity.District, 
Activity.[ProgramYear], [Activity].GWP;
AxxieD
  • 614
  • 1
  • 7
  • 28
  • Are you saying you get no records even if you completely remove the `WHERE` clause? – HansUp Jun 22 '15 at 18:22
  • The clause is still there, but all the fields are blank. – AxxieD Jun 22 '15 at 18:28
  • 1
    I don't understand what that means. Can you show us the SQL with a `WHERE` clause with all blank fields? – HansUp Jun 22 '15 at 18:31
  • I just updated the query in my post for when everything is empty. I think the query isn't updating. When I open it in table view, it's empty no matter how I filter it. – AxxieD Jun 22 '15 at 18:41
  • did you copy this query text? If so, you'll need to fix: `INNER OIN ActivityCash` – Beth Jun 22 '15 at 18:55
  • also fix:`Activity.GWP = Monitoring.` to read `Activity.GWP = Monitoring.gwp` – Beth Jun 22 '15 at 18:56
  • Those are both typos. I must have made them while formatting it. But they're goth correct in my actual query. – AxxieD Jun 22 '15 at 18:57

2 Answers2

2

In general, to debug these types of problems, try removing one table at a time from the FROM clause (and SELECT) until you get your results back.

Beth
  • 9,531
  • 1
  • 24
  • 43
0

Remove AND [Activity].[Designer] like '*' from the query.

phicon
  • 3,549
  • 5
  • 32
  • 62