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

  • 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.

    Thursday, July 19, 2018 2:12 PM

Answers

  • Try:

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

    • Marked as answer by stillanoob Thursday, July 19, 2018 5:22 PM
    Thursday, July 19, 2018 5:15 PM
  • @Hans

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


    You omitted the quote " after [Owned?]

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

    • Marked as answer by stillanoob Thursday, July 19, 2018 5:18 PM
    Thursday, July 19, 2018 5:16 PM
  •  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)

    • Marked as answer by stillanoob Thursday, July 19, 2018 5:45 PM
    Thursday, July 19, 2018 5:39 PM
  • =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.

    • Marked as answer by stillanoob Friday, July 20, 2018 2:33 PM
    Friday, July 20, 2018 12:11 PM

All replies

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

    Thursday, July 19, 2018 2:58 PM
  • 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.
    Thursday, July 19, 2018 4:46 PM
  • Hi,

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

    Just my 2 cents...

    Thursday, July 19, 2018 4:53 PM
  • 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.
    • Edited by stillanoob Thursday, July 19, 2018 5:02 PM
    Thursday, July 19, 2018 5:00 PM
  • 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)

    Thursday, July 19, 2018 5:06 PM
  • 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.

    Thursday, July 19, 2018 5:08 PM
  • @Hans

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

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



    • Edited by stillanoob Thursday, July 19, 2018 5:14 PM
    Thursday, July 19, 2018 5:13 PM
  • Try:

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

    • Marked as answer by stillanoob Thursday, July 19, 2018 5:22 PM
    Thursday, July 19, 2018 5:15 PM
  • @.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).

    Thursday, July 19, 2018 5:16 PM
  • @Hans

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


    You omitted the quote " after [Owned?]

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

    • Marked as answer by stillanoob Thursday, July 19, 2018 5:18 PM
    Thursday, July 19, 2018 5:16 PM
  • @Hans

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

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

    Thursday, July 19, 2018 5:18 PM
  • 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.

    Thursday, July 19, 2018 5:35 PM
  •  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)

    • Marked as answer by stillanoob Thursday, July 19, 2018 5:45 PM
    Thursday, July 19, 2018 5:39 PM
  • 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)

    Thursday, July 19, 2018 5:41 PM
  • 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'")

    • Edited by stillanoob Thursday, July 19, 2018 5:45 PM
    Thursday, July 19, 2018 5:44 PM
  • Thank you for the Access 101

    • Edited by stillanoob Thursday, July 19, 2018 5:53 PM
    Thursday, July 19, 2018 5:45 PM
  • 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

    Friday, July 20, 2018 11:46 AM
  • =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.

    • Marked as answer by stillanoob Friday, July 20, 2018 2:33 PM
    Friday, July 20, 2018 12:11 PM
  • Your first line of code worked! Thanks again. This is turning out to be an awesome thread.
    Friday, July 20, 2018 2:34 PM