How to create a string count of a certain value in one column in a table?

Question

I'm guessing that this is so simple I couldn't find an example online for this. So I have already made a query that accomplishes the result in table format. However on my main form I'd like an almost dashboard like view to literally display the NUMBER based off of the criteria of the query I made.

This is a project that I'm doing for fun and have a spreadsheet with an import feature on my form. I update the spreadsheet and just have to click the button on my form in Access and the table updates.

My query is literally to select all of the albums that I own in my music collection. For instance I have added all albums by bands that I don't own when I own an album as almost like a checklist. I'll use an example. I own all of the studio albums by Kreator except for about 3 of them. So then those three wouldn't be captured in my query.

The query is based off of the Column titled "Owned?" the value is either NULL or "Yes". I simply want to be able to display the count of "Yes" in a string format in a label or whatever on my main form.

I think this is a very straight-forward question please let me know if I need to re-word.

Answer 1

Place a text box in the form header or footer with Control Source

=Count([Owned])

or

=Abs(Sum([Owned]="Yes"))

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 2

Interesting when I use either of those in the textbox's Control Source I get "#Error" as a result. I will note that the column is "Count?" which I did change your code to include the "?" in the name.

Answer 3

Hi,

Did you enclose the name in square brackets? Spaces and special characters in names can mess with code, sometimes.

Just my 2 cents...

Answer 4

I sure did and no spaces this time haha.

Unless of course there's an actual way of inserting a Header of Footer on the Form. ................... In which case that would be why there's an error. Though I'm unaware of how to do so and if I were to build a dashboard off counts which I'd like to do then I don't know how having them only in headers and footers would be visually appealing lol.

Answer 5

Is your dashboard form unbound? If so, the expressions I posted won't work. Try

=DCount("[Owned?],"NameOfTableThatContainsOwned")

This does nopt have to be in the form header or footer.

(To add header/footer, right-click the bar that says Detail and click Form Header/Hooter)

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 6

Hi,

Of course, to insert a form header and footer, you can right-click on the form in design view and select Form Header/Footer.

Can you post the Record Source property of your form? An #Error message is a bit cryptic and hard to troubleshoot sometimes.

Answer 7

@Hans

=DCount("[Owned?],"tbl_List")

And I did add a header and put the text-box in there.

Answer 8

Try:

DCount("[Owned]","Your Table Name","[Owned]='Yes'")

Answer 9

@.theDBguy

Now everyone will really hate me. I don't know what you mean by record source for the form. If you mean by right clicking in an empty area on the form and clicking "Build Event" and to display that code, there is none.

Which I guess would answer the question above that the form is unbound and not bound to anything. (I'm not even fully understanding of bound and unbound objects).

Answer 10

@Hans

=DCount("[Owned?],"tbl_List")

You omitted the quote " after [Owned?]

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 11

@Hans

Thank you!!! That worked and is displaying the correct count now!

=DCount("[Owned?]","tbl_List")

Answer 12

I don't know if I'll need a new thread for this but it's a continuation off of the the above code just adding another condition

=DCount("[Genere]","tbl_List","[Genere]='Grunge'")

That does get me an accurate count of every occurrence that the word "Grunge" appears in the table. However all of those line items aren't "Yes" in the "Owned?" column. I'd like a count for all Grunge albums owned.

Answer 13

 I'd like a count for all Grunge albums owned.

To count all Grunge records that you own, use

=DCount("*", "tbl_List", "[Owned?]='Yes' And [Genere]='Grunge'")

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 14

A form has a property Record Source. It is the first property in the Data tab of the Property Sheet:

If this property is empty, we say that the form is unbound. Otherwise, we say that the form is bound to the table or query specified in the Record Source property.

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 15

I literally just figured it out myself based off of all of your guys input!!

=DCount("[Owned?]='Yes'","tbl_List","[Genere]='Grunge'")

@ Hans

your code worked as well!

=DCount("*", "tbl_List", "[Owned?]='Yes' And [Genere]='Grunge'")

Answer 16

Thank you for the Access 101

Answer 17

Are wildcards not allowed in the Control Source?

=DCount("*","tbl_List","[Owned?]='Yes' And [Year Released]='197*'")

Producing an error on the following syntax. Looking to count all albums that were released in the 70's

Answer 18

=DCount("*","tbl_List","[Owned?]='Yes' And [Year Released]='197*'")

Hi stillanoob,

You could try the LIKE operator:

      =DCount("*","tbl_List","[Owned?]='Yes' And [Year Released] LIKE '197*'" 

Perhaps you have to convert [Year Released] - which probably is an Integer - to a string-type:

     =DCount("*","tbl_List","[Owned?]='Yes' And CStr([Year Released]) LIKE '197*'")

Imb.

Answer 19

Your first line of code worked! Thanks again. This is turning out to be an awesome thread.