Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1235
  • Last Modified:

Access form not showing all records in base table

Loaded 229 records without any errors. All show up in table. However when open form only 143 records show up. Form is based on a query which select * from the table.
0
HKBoyz
Asked:
HKBoyz
  • 3
  • 2
  • 2
5 Solutions
 
DexstarCommented:
HKBoyz:

SELECT * doesn't mean "Select all records".  It means "select all fields".  Are you doing a join or anything with any other tables?  Post the full text of the query so we can have a look.

HTH,
Dex*
0
 
HKBoyzAuthor Commented:
The form is based on this query, where t_requirements is the table where data goes to:

SELECT t_requirements.*
FROM t_requirements
WHERE (((t_requirements.Process_ID)=IIf([forms]![f_requirements].[option].[value]=99,[process_id],([forms]![f_requirements].[option].[value]))) AND ((t_requirements.Module_ID)=IIf([forms]![f_requirements].[option1].[value]=99,[module_id],([forms]![f_requirements].[option1].[value]))) AND ((t_requirements.Finsys_id)=IIf([forms]![f_requirements].[option2].[value]=99,[finsys_id],([forms]![f_requirements].[option2].[value]))))
ORDER BY t_requirements.Process_ID;

The conditions are for option group filtering purpose, where 99 = ALL.
0
 
naivadCommented:
The WHERE clause is Filtering out some of your records.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
naivadCommented:
OK...

I actually built the thing...

Check your table for NULL values, they wont show...if the value is 0 it works, NULLS do not work
0
 
DexstarCommented:
naivad:
> Check your table for NULL values, they wont show...if the value is 0
> it works, NULLS do not work

Good catch!

HKBoyz:

Try this:

    SELECT t_requirements.*
    FROM t_requirements
    WHERE ((((t_requirements.Process_ID)=IIf([forms]![f_requirements].[option].[value]=99,[process_id],([forms]![f_requirements].[option].[value])))
    OR ([forms]![f_requirements].[option].[value]=99 AND IsNull([process_id]) = True))
    AND (((t_requirements.Module_ID)=IIf([forms]![f_requirements].[option1].[value]=99,[module_id],([forms]![f_requirements].[option1].[value])))
    OR ([forms]![f_requirements].[option1].[value]=99 AND IsNull([module_id]) = True))
    AND (((t_requirements.Finsys_id)=IIf([forms]![f_requirements].[option2].[value]=99,[finsys_id],([forms]![f_requirements].[option2].[value]))))
    OR ([forms]![f_requirements].[option2].[value]=99 AND IsNull([finsys_id]) = True))
    ORDER BY t_requirements.Process_ID;

I think I got it right.  I didn't recreate it, that's just off the top of my head.

-D*
0
 
DexstarCommented:
HKBoyz:

Maybe that isn't right.  I think it might need to be this:

    SELECT t_requirements.*
    FROM t_requirements
    WHERE ((((t_requirements.Process_ID)=IIf([forms]![f_requirements].[option].[value]=99,[process_id],([forms]![f_requirements].[option].[value])))
    OR ([forms]![f_requirements].[option].[value]=99 AND [process_id] IS NULL))
    AND (((t_requirements.Module_ID)=IIf([forms]![f_requirements].[option1].[value]=99,[module_id],([forms]![f_requirements].[option1].[value])))
    OR ([forms]![f_requirements].[option1].[value]=99 AND [module_id] IS NULL))
    AND (((t_requirements.Finsys_id)=IIf([forms]![f_requirements].[option2].[value]=99,[finsys_id],([forms]![f_requirements].[option2].[value]))))
    OR ([forms]![f_requirements].[option2].[value]=99 AND [finsys_id] IS NULL))
    ORDER BY t_requirements.Process_ID;

-D*
0
 
HKBoyzAuthor Commented:
Thanks, guys. All those columns are required so they all got values (ie. no null vlaues). My quesiton is: does the number of records ACCESS is showing at the bottom of the screen correctly reflect the toal number of records in the table? I thought I saw all the records ont he form, but the number is showing sth. different.
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

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