Dcount function (1 Viewer)

[TipsyWolf]

Hey guys, im stucked with Dcount. i have 2 questions

how do u return all values in certain filed. ? i was using

=Nz(DCount("riskcode";"Tmain";"riskcode = '1'");0)+
=Nz(DCount("riskcode";"Tmain";"riskcode = '2'");0)

but how do u return any values (like there is no criteria). i was trying to use

=Nz(DCount("riskcode";"Tmain";"riskcode = '*'");0)

and

=Nz(DCount("riskcode";"Tmain";"riskcode = ''");0)

but no luck for me here.

and the 2nd one is how do u return criteria using between ? i tried

=Nz(DCount("riskcode";"Tmain";"riskcode = between '1' and '7'");0)

but again no luck for me here as well.

 

[plog]

First, as long as is syntatically correct, DCount will return a value, so no need for the NZ around it.

Second, the criteria is optional, just don't include it if you don't want any:

DCount("FieldName", "TableName")

Third, numbers work with numeric comparison operators (>, < =, etc.). So if riskcode is a number, use those operators:

riskcode >= 1 AND riskcode<=7

Fourth, don't put numbers inside quote marks. In the above example I didn't put single quotes around 1 nor 7 and neither should you if riskcode is numeric.

Fifth, if riskcode isn't numeric, it should be and you have bigger issues than this DCount.

 

[TipsyWolf]

First, as long as is syntatically correct, DCount will return a value, so no need for the NZ around it.

Second, the criteria is optional, just don't include it if you don't want any:

DCount("FieldName", "TableName")

Third, numbers work with numeric comparison operators (>, < =, etc.). So if riskcode is a number, use those operators:

riskcode >= 1 AND riskcode<=7

Fourth, don't put numbers inside quote marks. In the above example I didn't put single quotes around 1 nor 7 and neither should you if riskcode is numeric.

Fifth, if riskcode isn't numeric, it should be and you have bigger issues than this DCount.

=DCount("FieldName", "TableName") worked for me nicely ! thanks !

but

=(DCount("riskcode";"Tmain";"riskcode >= 1 AND riskcode <=7"))

didn't work
maybe i did some syntatic mistake

 

[theDBguy]

Please confirm if [riskcode] is a numeric field or not.

 

[Pat Hartman]

If you want to return counts for each value of riskcode you use a totals query.

Select riskcode, Count(*) As CodeCount
From YourTable
Group by riskcode

This will give you the count as a list. If you want the count as one row with multiple columns, use the CrossTab wizard to create it. The CrossTab wizard requires three columns to do its magic so you will probably need to have a dummy column in the query. Make that first.

Select "row" as Dummy, riskcode From YourTable

Then use this query rather than the table to build your CrossTab

 

[plog]

"didn't work" is never helpful to us. Too ambigous. Did you get an error message? Results that were wrong? Cause your spouse to leave you for someone younger? Be specific in what occured to be wrong.

Syntactically your code is correct, unless riskcode is text. In design view of the table that riskcode is in, what datatype is it?

 

[Gasman]

Well the one that worked used a comma, and the one that does not a semi colon.?
Now the latter is a separator in some countries. Is it in yours?, as only one should work?

 

[TipsyWolf]

Please confirm if [riskcode] is a numeric field or not.

yeap. its numeric only. from 1 to 25

 

[TipsyWolf]

If you want to return counts for each value of riskcode you use a totals query.

Select riskcode, Count(*) As CodeCount
From YourTable
Group by riskcode

This will give you the count as a list. If you want the count as one row with multiple columns, use the CrossTab wizard to create it. The CrossTab wizard requires three columns to do its magic so you will probably need to have a dummy column in the query. Make that first.

Select "row" as Dummy, riskcode From YourTable

Then use this query rather than the table to build your CrossTab

i have a dashboard with big amount of statiscis i will put in it and i need to calc many different values which located in 2 tables. main one and sub.
later i will make every number in these field are clickable so a users see rows (like masterlist)

 

[theDBguy]

yeap. its numeric only. from 1 to 25

Hi. Thanks for responding, but I'm still not convinced yet. You just confirmed the "values," but I was asking for you to confirm the "field." You do that by telling us the Data Type of the Field as shown in the Table's Design View. Does it say Number or Short Text?

 

[TipsyWolf]

"didn't work" is never helpful to us. Too ambigous. Did you get an error message? Results that were wrong? Cause your spouse to leave you for someone younger? Be specific in what occured to be wrong.

Syntactically your code is correct, unless riskcode is text. In design view of the table that riskcode is in, what datatype is it?

im sorry for my short response. i was in a work site and ppl are rushing me
it says #error.
riskcode is numeric only

until i double check it and its

But !

the only numbers it stores are numeric , cause i have toggle bottons (which are only numbers) to put data in this fiels

i probably have to change data type of riskcode field, but i dont think it somehow matters for dcount as it looks for data rather for data type. correct me if im wrong.

 

[TipsyWolf]

Well the one that worked used a comma, and the one that does not a semi colon.?
Now the latter is a separator in some countries. Is it in yours?, as only one should work?

its semi colon in my region.

 

[TipsyWolf]

Hi. Thanks for responding, but I'm still not convinced yet. You just confirmed the "values," but I was asking for you to confirm the "field." You do that by telling us the Data Type of the Field as shown in the Table's Design View. Does it say Number or Short Text?

i used this code and it works

=Nz(DCount("riskcode";"Tmain";"riskcode = '1'");0)+
Nz(DCount("riskcode";"Tmain";"riskcode = '2'");0)+
Nz(DCount("riskcode";"Tmain";"riskcode = '3'");0)+
Nz(DCount("riskcode";"Tmain";"riskcode = '4'");0)+
Nz(DCount("riskcode";"Tmain";"riskcode = '5'");0)+
Nz(DCount("riskcode";"Tmain";"riskcode = '6'");0)+
Nz(DCount("riskcode";"Tmain";"riskcode = '7'");0)

now i know, i need to get rid of Nz (thanks go to @plog user). i will fix it later, but this code works for me fine , but

=(DCount("riskcode";"Tmain";"riskcode >= 1 AND riskcode <=7"))

it says an #error

 

[TipsyWolf]

Hi. Thanks for responding, but I'm still not convinced yet. You just confirmed the "values," but I was asking for you to confirm the "field." You do that by telling us the Data Type of the Field as shown in the Table's Design View. Does it say Number or Short Text?

i was so wrong big time. now the data type is number in design view. once i changed and

=(DCount("riskcode";"Tmain";"riskcode >= 1 AND riskcode <=7"))

worked for me easily !

thank you very much everyone here !! u r so helpful !! i would hug u if i could

 

[plog]

Hate to tell you this, but it did not work. The datatype absolutely matters in this.

As text, riskcode "19" is greater than "1" and less than "7". Thats because when computers compare text they do it character by character. The first character of "19" is "1" which means it will be less than any text that starts with character "7". When copmuters compare actual numbers they do it value by value, which of course makes 19 greater than 7.

You need to fix riskcode to the correct datatype or this DCount will not work--specifically by not producing the right results.

 

[Gasman]

im sorry for my short response. i was in a work site and ppl are rushing me
it says #error.
riskcode is numeric only

until i double check it and its
View attachment 113383

But !

the only numbers it stores are numeric , cause i have toggle bottons (which are only numbers) to put data in this fiels

View attachment 113384

i probably have to change data type of riskcode field, but i dont think it somehow matters for dcount as it looks for data rather for data type. correct me if im wrong.

You are completely wrong.
It is the type of data that defines the syntax. If you are only going to have number and Integer at that, use the correct numeric type.

 

[TipsyWolf]

You are completely wrong.
It is the type of data that defines the syntax. If you are only going to have number and Integer at that, use the correct numeric type.

thank you very much, sir !
i already learnt it in post #14. thanks for educating me in access !
much appriciated !

 

[TipsyWolf]

Hate to tell you this, but it did not work. The datatype absolutely matters in this.

As text, riskcode "19" is greater than "1" and less than "7". Thats because when computers compare text they do it character by character. The first character of "19" is "1" which means it will be less than any text that starts with character "7". When copmuters compare actual numbers they do it value by value, which of course makes 19 greater than 7.

You need to fix riskcode to the correct datatype or this DCount will not work--specifically by not producing the right results.

thank you very much !
u guys help me a lot and thank you for your time for answering quite simple questions and problems ! love it being here !

 

[theDBguy]

Glad to hear you got it sorted out. Good luck with your project.

 

[Pat Hartman]

Rather than hardcoding the values for the individual riskcodes and adding them all together, just count the whole set. Someone earlier gave you code for that and you said it worked so I don't understand why you are back to the individual domain functions.

The reason we don't hard code values this way is because it gets us into a perpetual maintenance loop and always increases the chances of error because you will almost certainly have multiple places where you need to adjust code as well as having to carefully avoid typos.

If you want to categorize riskcodes so that they are counted as "minor", "average", and "major" for example, add another column to the riskcode table so you can assign a category to each individual code. Then you join to the riskcode table in your query to pick up the category and use a query to display the three categories - either in a subform as a list or a subform as a crosstab.