How to find result "Total" - Expressions?!

[Jk45]

Hi. I have recently made a video database for a project, and have have come to a small problem. What I wanted to do was to, using a query search, find the total number of results found, in a form.

Basically, I have a query, in which I have the Film Name, Movie Cost e.t.c, with - Like ("*" & [Film Name], as the Expression (under Film Name).

When using the query, it retrieves results for what you have typed.

I use the query on a form "Film Search", which when clicked on Automatically brings up the Search Box. I also have another form "Films", which has a "Search Films" command button on it, run from a Macro. So basically everything works fine, but I when I search for a film, I want to have a text box, on the form, which has a count for the results.

For example, if I searched say "Shrek", and I got results for Shrek, Shrek 2and Shrek 3, the text box would say - " 3 results found".

This brings me to the conclusion of asking if I should do an expression, from the original query I am using, or to use Visual Basic, and more importantly - HOW?!?

Any help would be GREATLY appreciated, and I am sorry in advance if one, I have not explained my problem well enough, and two, this is in the wrong section.

Thanks in advance

Jk

 

[JoeyY]

Have you tried using =Count([Film Name]) in your text box control source?

 

[CraigDolphin]

You may also want to look into the DCount function to count records returned by your search query. You can use this in the control source of a textbox on your form.

 

[Jk45]

Thanks for the reply - Sorry I haven't replied quicker.

Joey - I have already tried the "=Count([Film Name]) in the text box control source", however it did not work.

Craig Dolphin - I will check if your solution works on Monday, when I can use my database.

I have also added to both your reputations, and again I must say thanyou for your reply.

I will also try to reply once I try the solution.

Thanks once more,

Jk

 

[boblarson]

I have already tried the "=Count([Film Name]) in the text box control source", however it did not work.

Just remember it won't work if the text box name is the same. So, if you have a text box named "Film Name" and your field is named "Film Name" aggregate expressions won't like it. You need to rename your text box something else (also, don't use spaces in field or object names, it will simplify your life immensely) and then use the FIELD name in the aggregate expression.

 

[Jk45]

Thanks Bob - I have only recently started Databases, so I am learning all the time, so thankyou for your tips.

I will try the expression on Monday when I get back to the database, as well as the other solutions posted here.

I have also added to your reputation for your reply.

Thanks again,

Jk