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

[Margie]

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

[ADezii]

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):

=DCount("*","tblMovies","[Status] = 'Seen'")

[Margie]

Set the Control Source of a Text Box on your Form to (assumes Table Name is tblMovies):

=DCount("*","tblMovies","[Status] = 'Seen'")

Many Thanks ADezii! It works perfectly. The exact code for me was:

=DCount("Status";"Movies";"[Status] = 'Seen'")

Thank you again for your help and quick response! Margie

[ADezii]

Many Thanks ADezii! It works perfectly. The exact code for me was:

=DCount("Status";"Movies";"[Status] = 'Seen'")

Thank you again for your help and quick response! Margie

You are quite welcome, Margie.