By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,644 Members | 2,423 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,644 IT Pros & Developers. It's quick & easy.

Count selected rows in a table and show in this in a form

P: 17
Hello all, after getting no where with my Access 2007 database problem for an entire week I figured I could use some outside help.

Wanting to skill myself in Access, I decided to make a movie database, something I always wanted. It took me some trial and error, but as it now stands, I made several tables (Companies, Cast, Genre, Country, Character, Director, Status, Movies)... The table Movies contains all the info on each movie while the others contain things like all the companies, countries, actors, etc.

Around this table (currently with 16 movies) I build a user friendly form where I can select anything I want with each new entry (the director, actors, year, etc...) and one field in that form is not giving the result I want.

In the table Status there is only 1 column (Status) and only 3 rows (Seen, Not Seen, See). So in the form I can select whichever applies to a specific movie. This works perfectly. In the form I added a field where I want it to automatically show the number of movies I've seen (currently 14 out of the 16). In the Property Field of that text box, I selected the tab Data. Next to Control Source I made a formula in the Expression Builder. The formula I used to see the total number of movies in the database (which took me a while to figure out) was:

=Count([Status])

This works as it shows the number 16. But I can't manage to see the number of movies I've seen (14). I've tried a lot of things, and all produce the number 0, -1 or an error. I figured the right code was:

=Count([Status]) Like 'Seen'

But this gives me back a 0. I even tried approaching the problem by several times making a query (I named it Viewed) containing all the data but only of the movies I've seen.
But when trying to linking the formula to the query I get a constant error code.

I hope the problem is reasonable clear to anyone reading this somewhat long story, considering access is totaly new to me and English isn't my main language. Should something be unclear or anything just let me know.

Greetings Margie
Jan 31 '08 #1
Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,607
Hello all, after getting no where with my Access 2007 database problem for an entire week I figured I could use some outside help.

Wanting to skill myself in Access, I decided to make a movie database, something I always wanted. It took me some trial and error, but as it now stands, I made several tables (Companies, Cast, Genre, Country, Character, Director, Status, Movies)... The table Movies contains all the info on each movie while the others contain things like all the companies, countries, actors, etc.

Around this table (currently with 16 movies) I build a user friendly form where I can select anything I want with each new entry (the director, actors, year, etc...) and one field in that form is not giving the result I want.

In the table Status there is only 1 column (Status) and only 3 rows (Seen, Not Seen, See). So in the form I can select whichever applies to a specific movie. This works perfectly. In the form I added a field where I want it to automatically show the number of movies I've seen (currently 14 out of the 16). In the Property Field of that text box, I selected the tab Data. Next to Control Source I made a formula in the Expression Builder. The formula I used to see the total number of movies in the database (which took me a while to figure out) was:

=Count([Status])

This works as it shows the number 16. But I can't manage to see the number of movies I've seen (14). I've tried a lot of things, and all produce the number 0, -1 or an error. I figured the right code was:

=Count([Status]) Like 'Seen'

But this gives me back a 0. I even tried approaching the problem by several times making a query (I named it Viewed) containing all the data but only of the movies I've seen.
But when trying to linking the formula to the query I get a constant error code.

I hope the problem is reasonable clear to anyone reading this somewhat long story, considering access is totaly new to me and English isn't my main language. Should something be unclear or anything just let me know.

Greetings Margie
Set the Control Source of a Text Box on your Form to (assumes Table Name is tblMovies):
Expand|Select|Wrap|Line Numbers
  1. =DCount("*","tblMovies","[Status] = 'Seen'")
Jan 31 '08 #2

P: 17
Set the Control Source of a Text Box on your Form to (assumes Table Name is tblMovies):
Expand|Select|Wrap|Line Numbers
  1. =DCount("*","tblMovies","[Status] = 'Seen'")
Many Thanks ADezii! It works perfectly. The exact code for me was:
Expand|Select|Wrap|Line Numbers
  1. =DCount("Status";"Movies";"[Status] = 'Seen'")
Thank you again for your help and quick response! Margie
Jan 31 '08 #3

ADezii
Expert 5K+
P: 8,607
Many Thanks ADezii! It works perfectly. The exact code for me was:
Expand|Select|Wrap|Line Numbers
  1. =DCount("Status";"Movies";"[Status] = 'Seen'")
Thank you again for your help and quick response! Margie
You are quite welcome, Margie.
Feb 1 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.