UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Dcount With Multiple Criteria, Access 2016    
 
   
jklinephd
post Jun 24 2019, 10:25 AM
Post#1



Posts: 67
Joined: 11-September 07
From: Missouri


I am using DCount to try to display some information in a Text Box on a Form.

My Form has a subform built from a query (QryAssign_Exam_ From_today) that displays - in datasheet form - the evaluations due in the next 6 months. It lists the date each evaluation is Due, the Month it is due, and the name of the Examiner.

On the Form I created a 3 Text boxes to display the total number of evaluations for each examiner over the 6 month period. I used DCount and it worked.

CODE
=DCount("*","QryAssign_Exam_ From_today","[Examiner]='Kline'")


This tells me how many evaluations I have due in the next 6 months.

I want to create a separate text box that will display the number of evaluations an examiner has due in a certain month within that 6 month period. I cannot get it to work. in the Query, the month is created using an expression (combination of Month and MonthName), the expression is MonthDue.

I thought that the criteria parameter for DCount would allow me to create a more complex expression that insures the values in the two relevant fields match. So I tried this

CODE
=DCount("*","QryAssign_Exam_ From_today","[Examiner]='Kline' AND [MonthDue]='July'")


I get a #Error result in the text box.

Just to play around with the expression format I created an expression in the query:

CODE
monthyes: ([Examiner]='Kline' And [MonthDue]='July')


Which displays a -1 next to each of my evaluations that are due in July. It displays a 0 next to the others.

So what is wrong with my DCount?

Thanks for all the help.
Go to the top of the page
 
cheekybuddha
post Jun 24 2019, 10:42 AM
Post#2


UtterAccess VIP
Posts: 11,463
Joined: 6-December 03
From: Telegraph Hill


Do you really have a space in the query name after the underscore and before 'From'?

--------------------


Regards,

David Marten
Go to the top of the page
 
jklinephd
post Jun 24 2019, 10:45 AM
Post#3



Posts: 67
Joined: 11-September 07
From: Missouri


Yes, I checked the database and unfortunately I do have a space. Unintended consequences of fat fingers.
Go to the top of the page
 
BruceM
post Jun 24 2019, 12:34 PM
Post#4


UtterAccess VIP
Posts: 7,921
Joined: 24-May 10
From: Downeast Maine


QUOTE
the month is created using an expression (combination of Month and MonthName)

Why, and what do you mean? You are using the month name only in the criteria expression. Is Month the numeric month?

Another thing: does the query limit the records so that it includes July from only one year? Can you use a date value instead?
Go to the top of the page
 
jklinephd
post Jun 24 2019, 12:58 PM
Post#5



Posts: 67
Joined: 11-September 07
From: Missouri


QUOTE
Why, and what do you mean? You are using the month name only in the criteria expression. Is Month the numeric month?

Another thing: does the query limit the records so that it includes July from only one year? Can you use a date value instead?


In the Query I have the Due Date of the evaluation. That is a full date. for my purposes I wanted to have just the month (so I could create a count of how many evaluations are due in a month). So I wrote this into the Query:

CODE
MonthDue: MonthName(Month([Next_due]))


The query does limit the number of records and shows only July (or any month) from one year. It basically lists all evaluations due in the next 6 months, so it only shows a month from one year.

So are you asking if I could get the name of the month from the full date (from the query field "DueDate")? I could, and It would be fine with me. I'm not certain how to do it from within the DCount.

Also, I have not entirely decided how to choose the month to display in the form. I have limited room. When I am showing monthly totals in the form, I was either going to use a combobox so that I could select the total for a given month. My preference would be to have room to display totals for 4-5 months, and have the months generated on the fly. For example. I open the form in July 2019 - it would display month totals - in text fields - for July, August, September, October, and November. If I open the form in September - it would display the same info for September, October, December, January, February.

Thanks for your help
This post has been edited by jklinephd: Jun 24 2019, 12:59 PM
Go to the top of the page
 
BruceM
post Jun 24 2019, 01:21 PM
Post#6


UtterAccess VIP
Posts: 7,921
Joined: 24-May 10
From: Downeast Maine


You could just do:

Month([Next_due]) = 7

You can show the month name in another column, but I would keep it simple for the criteria. But I don't think that is the real problem. Is Next_due a date/time field?

The error you described in your initial post could simply be that there is a space in the query name. You could try surrounding the name with square brackets -- [QryAssign_Exam_ From_today]

To tell the truth, I'm not quite following the description. It may be helpful to see the SQL from the query, with a description of the result you hope for and the result you are getting instead.
Go to the top of the page
 
jklinephd
post Jun 24 2019, 02:05 PM
Post#7



Posts: 67
Joined: 11-September 07
From: Missouri


Well it worked. I settled on getting the Month from the [Next_due] which is a field in query instead of the expression I created.

CODE
=DCount("*","QryAssign_Exam_ From_today","[Examiner]='Kline' AND Month([Next_due]) = 7")


Thanks for the tips and the help.

Go to the top of the page
 
BruceM
post Jun 25 2019, 06:27 AM
Post#8


UtterAccess VIP
Posts: 7,921
Joined: 24-May 10
From: Downeast Maine


Sometimes it is necessary to repeat the calculation rather than using the name of the calculated field, but glad to hear the simpler version of the criteria is getting it done. Good luck with the project.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th August 2019 - 02:08 PM