DCount Newb (1 Viewer)

[Erin M 2021]

I have a query[Appeal Gifts] with a column named [Constit]

Not sure what I'm doing wrong here: =DCount([Appeal Gifts].[Subtype], [Appeal Gifts], [Appeal Gifts], ([Subtype] is not null))

I get the error: The expression you entered has a function containing the wrong number of arguments.

 

[theDBguy]

Hi. The DCount() function expects String Arguments. For instance:

DCount("*", "TableName", "FieldName=SomeValue")

 

[plog]

Here's a good reference for DCount:

MS Access: DCount Function

This MSAccess tutorial explains how to use the Access DCount function with syntax and examples. The Microsoft Access DCount function returns the number of records from an Access table (or domain).

www.techonthenet.com

You've made a few mistakes, the one you are getting yelled at is for passing it too much information. DCount needs you to pass it at least 2 pieces of data and at most 3. You passed it 4.

DCount(Argument1, Argument2, Argument3)

An argument is a piece of data you pass it. They are seperated by commas. You have used 3 commas which means you passed it 4 pieces of data. Each of DCount's arguments are strings, which means they should have quote marks around them. Here's what you should be passing:

Argument1="FieldName", this is the field you want to count on
Argument2="DataSourceName", this is the table/query the field is in

Argument3 is a string that is used as criteria--you can check the reference for more info. The thing is, you don't need to use this one. DCount does not count NULL values so there's no reason to account for them in the criteria argument. You only need to construct your DCount with the field and query name.

Give it a shot and post back if you still have issues.

 

[conception_native_0123]

=DCount([Appeal Gifts].[Subtype], [Appeal Gifts], [Appeal Gifts], ([Subtype] is not null))

TRY:

=DCount("Subtype", "Appeal Gifts", "Subtype is not null")

 

[MajP]

Also be careful with your names. Do not use things that are vba function or access object names. SubType is OK but I had to check. Type is not OK. Also no spaces in names. Then you do not have to worry about always using square brackets [field name] when you do queries and code.

List of reserved words in Access - Microsoft 365 Apps

Lists the reserved words that you should not use in field, object, and variable names. It is not practical to provide a list of all reserved words, such as built-in function names or Access user-defined names.

docs.microsoft.com

-S
    SCREEN
    SECTION
    SELECT
    SET
    SetFocus
    SetOption
    SHORT
    SINGLE
    SMALLINT
    SOME
    SQL
    StDev, StDevP
    STRING
    Sum
-T
    TABLE
    TableDef, TableDefs
    TableID
    TEXT
    TIME, TIMESTAMP
    TOP
    TRANSFORM
    TRUE
    Type

 

[conception_native_0123]

Also no spaces in names

this is pretty much an industry standard. programmers who have been around a while, I don't think ever use spaces in the naming of anything. Unless I'm missing something.

 

[MajP]

this is pretty much an industry standard. programmers who have been around a while, I don't think ever use spaces in the naming of anything. Unless I'm missing something.

Probably 80% or more of the people who post on this site looking for help are not professional programmers. Most people here and most Access users in general are "power users" and enthusiasts. So yeah if you think otherwise, you are missing the point and you clearly pointed that out yourself. What you posted fails.

=DCount("Subtype", "Appeal Gifts", "Subtype is not null")

Should be

=DCount("Subtype", "[Appeal Gifts]", "Subtype is not null")

 

[conception_native_0123]

=DCount("Subtype", "Appeal Gifts", "Subtype is not null")

Should be

=DCount("Subtype", "[Appeal Gifts]", "Subtype is not null")

it doesn't matter maj. I tested it before posting it because I wasn't sure either. but let us not split hairs. the OP still hasn't gotten back yet.

 

[Erin M 2021]

Thanks to you both. Maybe DCount is not what I am wanting or perhaps there's an extra piece needed. I have the report grouped by a criteria (AppealCode) and for some reason when I add this field, it is just giving me total count for all rows in [Appeal Gifts]. Other calculations are working as they should in the group. Any Idea?

This is the current line that totals everything despite the grouping : =DCount("[Subtype]","[Appeal Gifts]","[Subtype] Is Not Null")

I will make note of spacing in the future!

 

[MajP]

Can you provide the complete SQL for the query? If your query is an aggregate query (group by) and you are grouping on appeal code this dcount is not grouped on that. So I would expect the answer you are getting. I assume you will have to structure your query differently, but need to see the overall query. Also may need to understand the fields in the involved tables.

 

[Erin M 2021]

Can you provide the complete SQL for the query? If your query is an aggregate query (group by) and you are grouping on appeal code this dcount is not grouped on that. So I would expect the answer you are getting. I assume you will have to structure your query differently, but need to see the overall query. Also may need to understand the fields in the involved tables.

Union Query:
(SELECT [AppealsGifts With Appeals].Gf_Apls_1_01_Amount AS AppealAmt, [AppealsGifts With Appeals].Gf_Apls_1_01_Appeal_ID AS [AppealID], [AppealsGifts With Appeals].Gf_Apls_1_01_Description AS AppealDesc, [AppealsGifts With Appeals].Gf_Apls_1_01_Ap_No_solicited AS NoSol, [AppealsGifts With Appeals].Gf_Apls_1_01_Ap_Appeal_category AS AppealCat, [AppealsGifts With Appeals].Gf_CnBio_ID AS Constit, [AppealsGifts With Appeals].Gf_Gift_subtype AS Subtype
FROM [AppealsGifts With Appeals]
WHERE ([AppealsGifts With Appeals].Gf_Apls_1_01_Appeal_ID) Is Not Null)

UNION ALL (SELECT [AppealsGifts With Appeals].Gf_Apls_1_02_Amount AS AppealAmt, [AppealsGifts With Appeals].Gf_Apls_1_02_Appeal_ID AS [AppealID], [AppealsGifts With Appeals].Gf_Apls_1_02_Description AS AppealDesc, [AppealsGifts With Appeals].Gf_Apls_1_02_Ap_No_solicited AS NoSol, [AppealsGifts With Appeals].Gf_Apls_1_02_Ap_Appeal_category AS AppealCat, [AppealsGifts With Appeals].Gf_CnBio_ID AS Constit, [AppealsGifts With Appeals].Gf_Gift_subtype AS Subtype
FROM [AppealsGifts With Appeals]
WHERE ([AppealsGifts With Appeals].Gf_Apls_1_02_Appeal_ID) Is Not Null)

UNION ALL (SELECT [AppealsGifts With Appeals].Gf_Apls_1_03_Amount AS AppealAmt, [AppealsGifts With Appeals].Gf_Apls_1_03_Appeal_ID AS [AppealID], [AppealsGifts With Appeals].Gf_Apls_1_03_Description AS AppealDesc, [AppealsGifts With Appeals].Gf_Apls_1_03_Ap_No_solicited AS NoSol, [AppealsGifts With Appeals].Gf_Apls_1_03_Ap_Appeal_category AS AppealCat, [AppealsGifts With Appeals].Gf_CnBio_ID AS Constit, [AppealsGifts With Appeals].Gf_Gift_subtype AS Subtype
FROM [AppealsGifts With Appeals]
WHERE ([AppealsGifts With Appeals].Gf_Apls_1_03_Appeal_ID) Is Not Null);

In the report, I have it grouped by AppealID. Within that group, I want a distinct count of the different Subtypes.

 

[MajP]

What if you make an aggregate query based on your union query.

Select AppealID, SubType, count(*) as SubTypeCount from UnionQueryName Group By AppealID, SubType

Can you show what that returns?

 

[plog]

This is now a non-issue. Your tables are improperly set up and you need to fix them. Please read up on normalization:

Database normalization - Wikipedia

en.wikipedia.org

You shouldn't have "sets" of fields in a table nor should you suffix fields with numbers (e.g. Gf_Apls_1_01_Amount, Gf_Apls_1_02_Amount Gf_Apls_1_03_Amount, etc.) to accomodate data. Tables should accomodate more data vertically (with more rows) and not horizontally (with more fields).

Instead of these 6 fields:

Desc1, Amount1, Desc2, Amount2, Desc3, Amount3
Screws, 2, Nails, 7, Bolts, 3

You simply need a table with 3 fields, like this:

Desc, Amount, SetNumber
Screws, 2, 1
Nails, 7, 2
Bolts, 3, 3

And that's only if the number you use as a suffix actually means something case--most times its just a way to name a field distinctly--in which case you don't need the SetNumber field. Then when you want the total of the amount you don't need to hack together a UNION query and then a DCOUNT, you simply do a SUM on the Amount field.

Restructure this thing, because more problems are on the horizon if you continue.